--10.2 GROUP BY
--语句
SELECT COLUMN1, COLUMN2
FROM TABLE1, TABLE2
WHERE CONDITIONS
GROUP BY COLUMN1, COLUMN2
ORDER BY COLUMN1, COLUMN2
--10.2.3创建分组和使用汇总函数
--从EMPLOYEE_TBL选择字段EMP_ID和CITY,根据CITY,EMP_ID排序
mysql> SELECT EMP_ID, CITY
FROM EMPLOYEE_TBL
GROUP BY CITY, EMP_ID;
+-----------+--------------+
| EMP_ID | CITY |
+-----------+--------------+
| 213764555 | WHITELAND |
| 220984332 | INDIANAPOLIS |
| 311549902 | GREENWOOD |
| 313782439 | INDIANAPOLIS |
| 442346889 | INDIANAPOLIS |
| 443679012 | INDIANAPOLIS |
+-----------+--------------+
6 rows in set
--返回EMP_ID和SALARY字段的总和,然后根据薪水和雇员ID进行分组
mysql> SELECT EMP_ID,SUM(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY ,EMP_ID;
+-----------+-------------+
| EMP_ID | SUM(SALARY) |
+-----------+-------------+
| 213764555 | 30000.00 |
| 220984332 | NULL |
| 311549902 | 40000.00 |
| 313782439 | 20000.00 |
| 442346889 | 30000.00 |
| 443679012 | NULL |
+-----------+-------------+
6 rows in set
--从EMPLOYEE_TBL里返回全部薪水总和
mysql> SELECT SUM(SALARY) AS TOTAL_SALARY
FROM EMPLOYEE_PAY_TBL;
+--------------+
| TOTAL_SALARY |
+--------------+
| 120000.00 |
+--------------+
1 row in set
--返回不同薪水的总和
mysql> SELECT SUM(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY;
+-------------+
| SUM(SALARY) |
+-------------+
| 60000.00 |
| NULL |
| 40000.00 |
| 20000.00 |
+-------------+
4 rows in set
--统计每个城市的记录数量
mysql> SELECT CITY, COUNT(*)
FROM EMPLOYEE_TBL
GROUP BY CITY;
+--------------+----------+
| CITY | COUNT(*) |
+--------------+----------+
| WHITELAND | 1 |
| INDIANAPOLIS | 4 |
| GREENWOOD | 1 |
+--------------+----------+
3 rows in set
--10.2.4
--GROUP BY中整数代表字段名称
mysql> SELECT YEAR(DATE_HIRE) AS YEAR_HIRED, SUM(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY 1;
+------------+-------------+
| YEAR_HIRED | SUM(SALARY) |
+------------+-------------+
| 2004 | 30000.00 |
| 2006 | NULL |
| 1999 | 40000.00 |
| 2007 | 20000.00 |
| 2000 | 30000.00 |
| 2001 | NULL |
+------------+-------------+
6 rows in set
--10.3 GROUP BY 和ORDER BY
--SELECT 列出的全部字段,除使用函数的,全部都要出现在GROUP BY子句中
mysql> SELECT LAST_NAME, FIRST_NAME, CITY
FROM EMPLOYEE_TBL
ORDER BY LAST_NAME, FIRST_NAME, CITY;
+-----------+------------+--------------+
| LAST_NAME | FIRST_NAME | CITY |
+-----------+------------+--------------+
| GLASS | BRANDON | WHITELAND |
| GLASS | JACOB | INDIANAPOLIS |
| PLEW | LINDA | INDIANAPOLIS |
| SPURGEON | TIFFANY | INDIANAPOLIS |
| STEPHENS | TINA | GREENWOOD |
| WALLACE | MARIAH | INDIANAPOLIS |
+-----------+------------+--------------+
6 rows in set
mysql> SELECT LAST_NAME, FIRST_NAME, CITY
FROM EMPLOYEE_TBL
GROUP BY LAST_NAME, FIRST_NAME, CITY;
+-----------+------------+--------------+
| LAST_NAME | FIRST_NAME | CITY |
+-----------+------------+--------------+
| GLASS | BRANDON | WHITELAND |
| WALLACE | MARIAH | INDIANAPOLIS |
| STEPHENS | TINA | GREENWOOD |
| GLASS | JACOB | INDIANAPOLIS |
| PLEW | LINDA | INDIANAPOLIS |
| SPURGEON | TIFFANY | INDIANAPOLIS |
+-----------+------------+--------------+
6 rows in set
mysql> SELECT CITY, LAST_NAME
FROM EMPLOYEE_TBL
ORDER
BY CITY, LAST_NAME;
+--------------+-----------+
| CITY | LAST_NAME |
+--------------+-----------+
| GREENWOOD | STEPHENS |
| INDIANAPOLIS | GLASS |
| INDIANAPOLIS | PLEW |
| INDIANAPOLIS | SPURGEON |
| INDIANAPOLIS | WALLACE |
| WHITELAND | GLASS |
+--------------+-----------+
6 rows in set
mysql> SELECT CITY, COUNT(*)
FROM EMPLOYEE_TBL
GROUP BY CITY
ORDER BY 1,2;
+--------------+----------+
| CITY | COUNT(*) |
+--------------+----------+
| GREENWOOD | 1 |
| INDIANAPOLIS | 4 |
| WHITELAND | 1 |
+--------------+----------+
3 rows in set
--10.4 CUBE和ROLLUP语句
--ROLLUP在全部分组数据的基础上,对其中一部分进行汇总。
--语法
--Oracle
GROUP BY ROLLUP(ordered column list of grouping sets)
--MySQL
GROUP BY ORDER COLUMN LIST OF GROUPING SETS WITH ROLLUP
--简单GROUP BY
mysql> SELECT CITY,ZIP, AVG(PAY_RATE), AVG(SALARY)
FROM EMPLOYEE_TBL E
INNER JOIN EMPLOYEE_PAY_TBL P
ON E.EMP_ID=P.EMP_ID
GROUP BY CITY, ZIP
ORDER BY CITY, ZIP;
+--------------+-------+---------------+-------------+
| CITY | ZIP | AVG(PAY_RATE) | AVG(SALARY) |
+--------------+-------+---------------+-------------+
| GREENWOOD | 47890 | NULL | 40000 |
| INDIANAPOLIS | 45734 | NULL | 20000 |
| INDIANAPOLIS | 46224 | 14.75 | 30000 |
| INDIANAPOLIS | 46234 | 15 | NULL |
| INDIANAPOLIS | 46741 | 11 | NULL |
| WHITELAND | 47885 | NULL | 30000 |
+--------------+-------+---------------+-------------+
6 rows in set
--ROLLUP的GROUP BY
mysql> SELECT CITY,ZIP, AVG(PAY_RATE), AVG(SALARY)
FROM EMPLOYEE_TBL E
INNER JOIN EMPLOYEE_PAY_TBL P
ON E.EMP_ID=P.EMP_ID
GROUP BY CITY, ZIP WITH ROLLUP;
+--------------+-------+---------------+-------------+
| CITY | ZIP | AVG(PAY_RATE) | AVG(SALARY) |
+--------------+-------+---------------+-------------+
| GREENWOOD | 47890 | NULL | 40000 |
| GREENWOOD | NULL | NULL | 40000 |
| INDIANAPOLIS | 45734 | NULL | 20000 |
| INDIANAPOLIS | 46224 | 14.75 | 30000 |
| INDIANAPOLIS | 46234 | 15 | NULL |
| INDIANAPOLIS | 46741 | 11 | NULL |
| INDIANAPOLIS | NULL | 13.583333 | 25000 |
| WHITELAND | 47885 | NULL | 30000 |
| WHITELAND | NULL | NULL | 30000 |
| NULL | NULL | 13.583333 | 30000 |
+--------------+-------+---------------+-------------+
10 rows in set
--1、GREENWOOD | NULL | NULL | 40000 | 对GREENWOOD合计
--2、INDIANAPOLIS | NULL | 13.583333 | 25000 | 对INDIANAPOLIS合计
--3、WHITELAND | NULL | NULL | 30000 | 对WHITELAND 合计
--4、NULL | NULL | 13.583333 | 30000 | 对全部合计
--CUBE的GROUP BY(MySQL不支持)
SELECT CITY,ZIP, AVG(PAY_RATE), AVG(SALARY)
FROM EMPLOYEE_TBL E
INNER JOIN EMPLOYEE_PAY_TBL P
ON E.EMP_ID=P.EMP_ID
GROUP BY CUBE(CITY, ZIP);
--10.5 HAVING子句
--语法
SELECT COLUMN1, COLUMN2
FROM TABLE1, TABLE2
WHERE CONDITIONS
GROUP BY COLUMN1, COLUMN2
HAVING CONDITIONS
ORDER BY COLUMN1, COLUMN2
SELECT CITY, AVG(PAY_RATE), AVG(SALARY)
FROM EMPLOYEE_PAY_TBL
WHERE CITY <> 'GREENWOOD'
GROUP BY CITY
HAVING AVG(SALARY) > 20000
ORDER BY 3;
--10.8 实践
--10.8.1.a
SELECT SUM(SALARY), EMP_ID
FROM EMPLOYEE_PAY_TBL
GROUP BY 1 AND 2;
--NO.1:GROUP by 1,2;NO.2:SUM(SALARY)不能GROUP BY.
--修改
mysql> SELECT SUM(SALARY), EMP_ID
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY, EMP_ID;
+-------------+-----------+
| SUM(SALARY) | EMP_ID |
+-------------+-----------+
| 30000.00 | 213764555 |
| NULL | 220984332 |
| 40000.00 | 311549902 |
| 20000.00 | 313782439 |
| 30000.00 | 442346889 |
| NULL | 443679012 |
+-------------+-----------+
6 rows in set
--10.8.1.b
mysql> SELECT EMP_ID, MAX(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY SALARY, EMP_ID;
+-----------+-------------+
| EMP_ID | MAX(SALARY) |
+-----------+-------------+
| 213764555 | 30000 |
| 220984332 | NULL |
| 311549902 | 40000 |
| 313782439 | 20000 |
| 442346889 | 30000 |
| 443679012 | NULL |
+-----------+-------------+
6 rows in set
--10.8.1.c
SELECT EMP_ID,COUNT(SALARY)
FROM EMPLOYEE_PAY_TBL
ORDER BY EMP_ID
GROUP BY SALARY;
mysql> SELECT EMP_ID,COUNT(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY EMP_ID
ORDER BY EMP_ID;
--先GROUP BY再ORDER BY;
+-----------+---------------+
| EMP_ID | COUNT(SALARY) |
+-----------+---------------+
| 213764555 | 1 |
| 220984332 | 0 |
| 311549902 | 1 |
| 313782439 | 1 |
| 442346889 | 1 |
| 443679012 | 0 |
+-----------+---------------+
6 rows in set
--10.8.1.d
mysql> SELECT YEAR(DATE_HIRE) AS YEAR_HIRED, SUM(SALARY)
FROM EMPLOYEE_PAY_TBL
GROUP BY 1
HAVING SUM(SALARY) > 20000;
+------------+-------------+
| YEAR_HIRED | SUM(SALARY) |
+------------+-------------+
| 2004 | 30000.00 |
| 1999 | 40000.00 |
| 2000 | 30000.00 |
+------------+-------------+
3 rows in set
--10.8.2
--10.8.2.1
mysql> SELECT CITY
FROM EMPLOYEE_TBL;
+--------------+
| CITY |
+--------------+
| WHITELAND |
| INDIANAPOLIS |
| GREENWOOD |
| INDIANAPOLIS |
| INDIANAPOLIS |
| INDIANAPOLIS |
+--------------+
6 rows in set
--10.8.2.2
mysql> SELECT CITY, COUNT(*)
FROM EMPLOYEE_TBL
GROUP BY CITY;
+--------------+----------+
| CITY | COUNT(*) |
+--------------+----------+
| WHITELAND | 1 |
| INDIANAPOLIS | 4 |
| GREENWOOD | 1 |
+--------------+----------+
3 rows in set
--10.8.2.3
--WHERE是查询的主过滤器,HAVING是在GROUP BY(分组)后进行过滤
mysql> SELECT CITY, COUNT(*)
FROM EMPLOYEE_TBL
GROUP BY CITY
HAVING COUNT(*) > 1;
+--------------+----------+
| CITY | COUNT(*) |
+--------------+----------+
| INDIANAPOLIS | 4 |
+--------------+----------+
1 row in set
--10.8.2.4
mysql> SELECT CITY, COUNT(*)
FROM EMPLOYEE_TBL
GROUP BY CITY
ORDER BY COUNT(*) DESC;
+--------------+----------+
| CITY | COUNT(*) |
+--------------+----------+
| INDIANAPOLIS | 4 |
| GREENWOOD | 1 |
| WHITELAND | 1 |
+--------------+----------+
3 rows in set
--10.8.2.5
SELECT AVG(PAY_RATE), SALARY, CITY
FROM EMPLOYEE_PAY_TBL
GROUP BY CITY;
--10.8.2.6
SELECT AVG(SALARY), CITY
FROM EMPLOYEE_PAY_TBL
GROUP BY CITY
HAVING AVG(SALARY) > 20000;