SQL – 分组依据

SQL GROUP BY子句与 SELECT 语句配合使用，以将相同的数据分组。此 GROUP BY 子句在 SELECT 语句中的 WHERE 子句之后，并在 ORDER BY 子句之前。

句法

GROUP BY 子句的基本语法显示在以下代码块中。GROUP BY 子句必须跟在 WHERE 子句中的条件之后，并且必须在 ORDER BY 子句之前（如果使用）。

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

例子

考虑 CUSTOMERS 表有以下记录 –

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

如果您想知道每个客户的工资总额，那么 GROUP BY 查询将如下所示。

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
   GROUP BY NAME;

这将产生以下结果 –

+----------+-------------+
| NAME     | SUM(SALARY) |
+----------+-------------+
| Chaitali |     6500.00 |
| Hardik   |     8500.00 |
| kaushik  |     2000.00 |
| Khilan   |     1500.00 |
| Komal    |     4500.00 |
| Muffy    |    10000.00 |
| Ramesh   |     2000.00 |
+----------+-------------+

现在，让我们看一个表，其中 CUSTOMERS 表具有以下重复名称的记录 –

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Ramesh   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | kaushik  |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

现在，如果您想知道每个客户的工资总额，那么 GROUP BY 查询将如下所示 –

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS
   GROUP BY NAME;

这将产生以下结果 –

+---------+-------------+
| NAME    | SUM(SALARY) |
+---------+-------------+
| Hardik  |     8500.00 |
| kaushik |     8500.00 |
| Komal   |     4500.00 |
| Muffy   |    10000.00 |
| Ramesh  |     3500.00 |
+---------+-------------+