Mysql group by用法

select * from emp;+-----+------+---------+----+----------+-------+-------+------+|empno|ename |job|mgr |hiredate|sal|comm|deptno|+-----+------+---------+----+----------+-------+-------+------+|7369 |S

鸭梨山大哎

4982人浏览 · 2021-04-18 11:13:39

鸭梨山大哎 · 2021-04-18 11:13:39 发布

select * from emp;

+-----+------+---------+----+----------+-------+-------+------+
|empno|ename |job      |mgr |hiredate  |sal    |comm   |deptno|
+-----+------+---------+----+----------+-------+-------+------+
|7369 |SMITH |CLERK    |7902|1980-12-17|1000.00|NULL   |20    |
|7499 |ALLEN |SALESMAN |7698|1981-02-20|1500.00|300.00 |30    |
|7521 |WARD  |SALESMAN |7698|1981-02-22|1250.00|500.00 |30    |
|7566 |JONES |MANAGER  |7839|1981-04-02|2975.00|NULL   |20    |
|7654 |MARTIN|SALESMAN |7698|1981-09-28|1250.00|1400.00|30    |
|7698 |BLAKE |MANAGER  |7839|1981-05-01|2850.00|NULL   |30    |
|7782 |CLARK |MANAGER  |7839|1981-06-09|2450.00|NULL   |10    |
|7788 |SCOTT |ANALYST  |7566|1987-04-19|3000.00|NULL   |20    |
|7839 |KING  |PRESIDENT|NULL|1981-11-17|5000.00|NULL   |10    |
|7844 |TURNER|SALESMAN |7698|1981-09-08|1500.00|0.00   |30    |
|7876 |ADAMS |CLERK    |7788|1987-05-23|1100.00|NULL   |20    |
|7900 |JAMES |CLERK    |7698|1981-12-03|950.00 |NULL   |30    |
|7902 |FORD  |ANALYST  |7566|1981-12-03|3000.00|NULL   |20    |
|7934 |MILLER|CLERK    |7782|1982-01-23|1300.00|NULL   |10    |
+-----+------+---------+----+----------+-------+-------+------+

group by可以单独使用

group by用于分组
group by未必要配合聚合函数使用,也可以单独使用, 这样的话取每组的第一行

select * from emp group by job;
+-----+-----+---------+----+----------+-------+------+------+
|empno|ename|job      |mgr |hiredate  |sal    |comm  |deptno|
+-----+-----+---------+----+----------+-------+------+------+
|7788 |SCOTT|ANALYST  |7566|1987-04-19|3000.00|NULL  |20    |
|7369 |SMITH|CLERK    |7902|1980-12-17|1000.00|NULL  |20    |
|7566 |JONES|MANAGER  |7839|1981-04-02|2975.00|NULL  |20    |
|7839 |KING |PRESIDENT|NULL|1981-11-17|5000.00|NULL  |10    |
|7499 |ALLEN|SALESMAN |7698|1981-02-20|1500.00|300.00|30    |
+-----+-----+---------+----+----------+-------+------+------+

可以用数字代表列

比如如下表示用第三列分组(select后第三个,从1开始)

select * from emp group by 3;
+-----+-----+---------+----+----------+-------+------+------+
|empno|ename|job      |mgr |hiredate  |sal    |comm  |deptno|
+-----+-----+---------+----+----------+-------+------+------+
|7788 |SCOTT|ANALYST  |7566|1987-04-19|3000.00|NULL  |20    |
|7369 |SMITH|CLERK    |7902|1980-12-17|1000.00|NULL  |20    |
|7566 |JONES|MANAGER  |7839|1981-04-02|2975.00|NULL  |20    |
|7839 |KING |PRESIDENT|NULL|1981-11-17|5000.00|NULL  |10    |
|7499 |ALLEN|SALESMAN |7698|1981-02-20|1500.00|300.00|30    |
+-----+-----+---------+----+----------+-------+------+------+

group by后可以用别名

select empno, ename, job as work
from emp
group by work;
+-----+-----+---------+
|empno|ename|work     |
+-----+-----+---------+
|7788 |SCOTT|ANALYST  |
|7369 |SMITH|CLERK    |
|7566 |JONES|MANAGER  |
|7839 |KING |PRESIDENT|
|7499 |ALLEN|SALESMAN |
+-----+-----+---------+

用于聚合

-- 这块和hive是不同的,hive不能这么写
select *,sum(sal) as total_sal from emp group by job;
+-----+-----+---------+----+----------+-------+------+------+---------+
|empno|ename|job      |mgr |hiredate  |sal    |comm  |deptno|total_sal|
+-----+-----+---------+----+----------+-------+------+------+---------+
|7788 |SCOTT|ANALYST  |7566|1987-04-19|3000.00|NULL  |20    |6000.00  |
|7369 |SMITH|CLERK    |7902|1980-12-17|1000.00|NULL  |20    |4350.00  |
|7566 |JONES|MANAGER  |7839|1981-04-02|2975.00|NULL  |20    |8275.00  |
|7839 |KING |PRESIDENT|NULL|1981-11-17|5000.00|NULL  |10    |5000.00  |
|7499 |ALLEN|SALESMAN |7698|1981-02-20|1500.00|300.00|30    |5500.00  |
+-----+-----+---------+----+----------+-------+------+------+---------+

select job,sum(sal) as total_sal from emp group by job;
+---------+---------+
|job      |total_sal|
+---------+---------+
|ANALYST  |6000.00  |
|CLERK    |4350.00  |
|MANAGER  |8275.00  |
|PRESIDENT|5000.00  |
|SALESMAN |5500.00  |
+---------+---------+