oracle常见集合函数,Oracle-学习笔记（==》集合函数与分组四）

-- 聚集函数 配合分组语句 group by

-- 显示最高分

SELECT MAX(sscore) FROM db.`student`;

-- 显示最高分学生的信息min max

SELECT * FROM db.`student` WHERE sscore IN(SELECT MAX(sscore) FROM db.`student`);

-- 统计人数 (不是信息)

SELECT COUNT(*) FROM db.`student`;

SELECT COUNT(*) FROM db.`student` WHERE sscore<60;;

SELECT AVG(sscore) 平均分,MAX(sscore) 最高分,MIN(sscore) 最低分 FROM student;

SELECT COUNT(*) FROM student WHERE sscore IS NULL;

SELECT COUNT(*) FROM student WHERE saddress IS NULL;

UPDATE student SET sscore=NULL WHERE sscore=0;

SELECT SUM(sage)FROM student;

SELECT AVG(sage)FROM student;

SELECT * FROM student;

-- 统计各地区的人数

SELECT COUNT(*) FROM student WHERE saddress='郑州';

SELECT sscore 分数,COUNT(*) 人数

FROM student

WHERE saddress IS NOT NULL -- 查询条件

GROUP BY sscore -- 分组字段

HAVING COUNT(*)>4 -- 分组条件(符合条件才会分组)

ORDER BY COUNT(*) DESC;-- desc降序排列

SELECT saddress,COUNT(*),MAX(sscore) FROM student GROUP BY saddress;

-- 并集

-- 求出郑州最高分学生信息

SELECT * FROM student

WHERE saddress='郑州'

AND sscore IN(SELECT MAX(sscore) FROM student WHERE saddress='郑州');

-- 上海最高分学生信息

SELECT * FROM student

WHERE saddress='上海'

AND sscore IN(SELECT MAX(sscore) FROM student WHERE saddress='上海');

------------------------------------

SELECT * FROM student

WHERE saddress='郑州'

AND sscore IN(SELECT MAX(sscore) FROM student WHERE saddress='郑州')

UNION

SELECT * FROM student

WHERE saddress='武汉'

AND sscore IN(SELECT MAX(sscore) FROM student WHERE saddress='武汉');