SQL GROUP BY(分組)

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

現在,讓我們有如下表,客戶表中有以下重名記錄:

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

0 條評論,你可以發表評論,我們會進行改進
Comment author placeholder