MySQL | 分组查询的应用

SELECT deptno,AVG(sal) FROM t_emp
GROUP BY deptno;

SELECT deptno,ROUND(AVG(sal)) FROM t_emp
GROUP BY deptno;
-- ROUND 取整

SELECT deptno,job,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno,job
ORDER BY deptno;

SELECT deptno,job,COUNT(*),AVG(sal) 
FROM t_emp
GROUP BY deptno,job
ORDER BY deptno

SELECT deptno,COUNT(*),AVG(sal)
FROM t_emp GROUP BY deptno;

SELECT dept_no,COUNT(*),AVG(sal),sal
FROM t_emp GROUP BY deptno;
-- XXXXXXXXXXXXXX

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

SELECT
	deptno,COUNT(*),AVG(sal),MAX(sal),MIN(sal)
FROM t_emp GROUP BY deptno WITH ROLLUP
-- WITH ROLLUP 汇总

SELECT deptno,AVG(sal),SUM(sal),MAX(sal),MIN(sal),count(*)
FROM t_emp
GROUP BY deptno WITH ROLLUP

SELECT deptno,GROUP_CONCAT(ename),COUNT(*)
FROM t_emp WHERE sal>=2000
GROUP BY deptno;

SELECT deptno,COUNT(*),GROUP_CONCAT(ename)
FROM t_emp
WHERE sal>=2000
GROUP BY deptno;