PostgreSQL-6-数据分组

基本语法

SELECT column-list FROM table_name

WHERE [ conditions ]

GROUP BY column1,column2

HAVING [ conditions ]

ORDER BY column1,column2

注意顺序 → WHERE - GROUP BY - HAVING - ORDER BY

?

SELECT classno,COUNT(studentname) FROM student GROUP BY classno;? 按照classno字段，分组汇总学生数量

SELECT classno,MIN(age),AVG(age) FROM student GROUP BY classno;? 查看每个班级年龄最小值/均值

INSERT INTO company3 VALUES(7,‘pual‘,2000);

INSERT INTO company3 VALUES(8,‘allen‘,3000);

INSERT INTO company3 VALUES(9,‘teddy‘,20000);

SELECT name,SUM(salary) FROM company3 GROUP BY name;? 多插入一些重复名称的数据，汇总每个员工的总薪水

?

分组+排序

SELECT classno,COUNT(studentname) FROM student GROUP BY classno ORDER BY classno; 按照classno排序

SELECT name,SUM(salary) FROM company3 GROUP BY name ORDER BY SUM(salary);? 按照总薪水排序

?

过滤分组，WHERE

SELECT classno,COUNT(studentname) FROM student

?????? WHERE classno > 2

?????? GROUP BY classno; WHERE作用与分组前，这里先筛选classno>2的数据，再分组

?????? 注意，这里如果书写：WHERE COUNT(studentname) > 1 就会报错

?

过滤分组，HAVING

SELECT classno,COUNT(studentname) FROM student

?????? GROUP BY classno

?????? HAVING COUNT(studentname) > 1; HAVING主要用于过滤分组，且是在分组后进行过滤

?????? 所以一般对于分组的条件过滤，都用HAVING