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 | +---------+-------------+