最新要闻

广告

手机

iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?

iphone11大小尺寸是多少?苹果iPhone11和iPhone13的区别是什么?

警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞:犯罪嫌疑人已投案

家电

读SQL进阶教程笔记09_HAVING上

来源:博客园


【资料图】

1.HAVING子句的用法

1.1.学习SQL时最大的阻碍就是我们已经习惯了的面向过程语言的思考方式(排序、循环、条件分支、赋值等)

1.2.只有习惯了面向集合的思考方式,才能真正地学好它

1.3.帮助我们顺利地忘掉面向过程语言的思考方式并理解SQL面向集合特性的最为有效的方法

1.4.HAVING子句的处理对象是集合而不是记录

1.4.1.如果一个实体对应着一行数据→那么就是元素,所以使用WHERE子句

1.4.2.如果一个实体对应着多行数据→那么就是集合,所以使用HAVING子句

1.5.HAVING子句可以通过聚合函数(特别是极值函数)针对集合指定各种条件

1.5.1.

1.5.2.如果通过CASE表达式生成特征函数,那么无论多么复杂的条件都可以描述

2.点名

2.1.示例

2.1.1.

2.1.1.1.-- 用谓词表达全称量化命题

SELECT team_id, member FROM Teams T1 WHERE NOT EXISTS (SELECT * FROM Teams T2 WHERE T1.team_id = T2.team_id AND status <>’待命’);
2.1.1.1.1.“所有队员都处于待命状态”=“不存在不处于待命状态的队员”
2.1.1.1.2.查询性能很好
2.1.1.1.3.结果中能体现出队员信息

2.1.1.2.-- 用集合表达全称量化命题(1)

SELECT team_id FROM Teams GROUP BY team_id HAVING COUNT(*) = SUM(CASE WHEN status =’待命’ THEN 1 ELSE 0 END);
2.1.1.2.1.代码很简洁
2.1.1.2.2.使用的是特征函数的方法

2.1.1.3.-- 用集合表达全称量化命题(2)

SELECT team_id FROM Teams GROUP BY team_id HAVING MAX(status) =’待命’ AND MIN(status) =’待命’;
2.1.1.3.1.性能更好
2.1.1.3.1.1.极值函数可以使用参数字段的索引
2.1.1.3.2.如果元素最大值和最小值相等,那么这个集合中肯定只有一种值

2.1.1.4.-- 列表显示各个队伍是否所有队员都在待命

SELECT team_id, CASE WHEN MAX(status) =’待命’AND MIN(status) =’待命’ THEN ’全都在待命’ ELSE’队长!人手不够’END AS status FROM Teams GROUP BY team_id;
2.1.1.4.1.条件移到SELECT子句后,查询可能就不会被数据库优化了

3.单重集合

3.1.示例

3.1.1.

3.1.1.1.-- 选中材料存在重复的生产地

SELECT center FROM Materials GROUP BY center HAVING COUNT(material) <> COUNT(DISTINCT material);
3.1.1.1.1.
SELECT center,          CASE WHEN COUNT(material) <> COUNT(DISTINCT material) THEN’存在重复’              ELSE’不存在重复’END AS status      FROM Materials     GROUP BY center;

3.1.1.2.--存在重复的集合:使用EXISTS

SELECT center, material FROM Materials M1 WHERE EXISTS (SELECT * FROM Materials M2 WHERE M1.center = M2.center AND M1.receive_date <> M2.receive_date AND M1.material = M2.material);

3.2.在数学中,通过GROUP BY生成的子集有一个对应的名字,叫作划分(partition)

3.2.1.集合论和群论中的重要概念,指的是将某个集合按照某种规则进行分割后得到的子集

3.2.2.这些子集相互之间没有重复的元素,而且它们的并集就是原来的集合

4.寻找缺失的编号:升级版

4.1.示例

4.1.1.-- 如果有查询结果,说明存在缺失的编号

SELECT’存在缺失的编号’AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq);

4.1.1.1.有一个前提条件,即数列的起始值必须是1

4.1.2.-- 如果有查询结果,说明存在缺失的编号:只调查数列的连续性

SELECT’存在缺失的编号’ AS gap FROM SeqTbl HAVING COUNT(*) <> MAX(seq) - MIN(seq) + 1 ;

4.1.3.-- 不论是否存在缺失的编号都返回一行结果

SELECT CASE WHEN COUNT(*) = 0 THEN ’表为空’ WHEN COUNT(*) <> MAX(seq) - MIN(seq) + 1 THEN ’存在缺失的编号’ ELSE’连续’END AS gap FROM SeqTbl;

4.1.4.-- 查找最小的缺失编号:表中没有1时返回1

SELECT CASE WHEN COUNT(*) = 0 OR MIN(seq) > 1 -- 最小值不是1时→返回1 THEN 1 ELSE (SELECT MIN(seq +1) -- 最小值是1时→返回最小的缺失编号 FROM SeqTbl S1 WHERE NOT EXISTS (SELECT * FROM SeqTbl S2 WHERE S2.seq = S1.seq + 1)) END FROM SeqTbl;

5.为集合设置详细的条件

5.1.示例

5.1.1.

5.1.1.1.查询出75%以上的学生分数都在80分以上的班级

5.1.1.1.1.
SELECT class      FROM TestResults  GROUP BY class    HAVING COUNT(*) * 0.75          <= SUM(CASE WHEN score >= 80                      THEN 1                      ELSE 0 END) ;

5.1.1.2.查询出分数在50分以上的男生的人数比分数在50分以上的女生的人数多的班级

5.1.1.2.1.
SELECT class      FROM TestResults  GROUP BY class    HAVING SUM(CASE WHEN score >= 50 AND sex =’男’                    THEN 1                    ELSE 0 END)        > SUM(CASE WHEN score >= 50 AND sex =’女’                    THEN 1                    ELSE 0 END) ;

5.1.1.3.查询出女生平均分比男生平均分高的班级

5.1.1.3.1.-- 比较男生和女生平均分的SQL语句(1):对空集使用AVG后返回0
SELECT class FROM TestResults GROUP BY class HAVING AVG(CASE WHEN sex =’男’ THEN score ELSE 0 END) < AVG(CASE WHEN sex =’女’ THEN score ELSE 0 END) ;
5.1.1.3.2.-- 比较男生和女生平均分的SQL语句(2):对空集求平均值后返回NULL
SELECT class FROM TestResults GROUP BY class HAVING AVG(CASE WHEN sex =’男’ THEN score ELSE NULL END) < AVG(CASE WHEN sex =’女’ THEN score ELSE NULL END) ;
5.1.1.3.2.1.根据标准SQL的定义,对空集使用AVG函数时,结果会返回NULL

关键词: