SQL进阶教程第一章----CASE表达式之 在CASE表达式中使用聚合函数
接下来我们学习一套新的掌法,实例:假设这里有一张显示了学生及其加入的摄入安的一览表,StudentClub,表的主键是“学生、社团ID”,存储了学生和社团之间的多对多的关系。
有的学会加入了多个社团,有的只是加入了某个社团,对于加入了多个社团的学生,我们通过将其“主社团标志”列设置为Y或者N来表明哪个社团是他的主社团,对于只是加入了一个社团的学生,我们将其“主社团标志”列设置为N。
现在需求如下:
1.如果学生只是加入了一个社团,则显示该学生id和社团id
2.如果学生加入了多个社团,则显示该学生id和主社团id
将两个结果结合到一张表里面:
对此,第一种错误方式:
使用了UNION进行连接,但是不知道为什么使用UNION之后,记录少了一条,可能是对UINION的使用还不够透彻,等UION学习之后再来分析此问题
SELECT std_id, MAX(club_id) AS main_club FROM StudentClub GROUP BY std_id HAVING COUNT(*) = 1; UNION SELECT std_id, club_id AS main_club FROM studentclub WHERE main_club_flg = true;
第二种错误方式,我在CASE表达式中使用了聚合函数,但是仍然有错-:
SELECT std_id, CASE WHEN COUNT(*) =1 THEN club_id WHEN COUNT(*) >1 THEN CASE WHEN main_club_flg = true THEN club_id ELSE NULL END ELSE NULL END AS main_club FROM StudentClub GROUP BY std_id
来看下标准答案:
SELECT std_id, CASE WHEN COUNT(*) =1 THEN MAX(club_id) ELSE MAX(CASE WHEN main_club_flg = true THEN club_id ELSE NULL END) END AS main_club FROM StudentClub GROUP BY std_id
可以看到第二个错误方式和标准答案之间的差别在于对最外层的CASE表达式的分条件处理,分析下第二种错误方式的蕴含式:
count(*) >1为P , main_club_flg = true为Q,P为真的时候,Q为真,返回club_id;
P为真 Q不确定发的时候,返回null;
啊啊啊啊,还是不清楚为什么会返回错误。!!!!
这条SQL语句在CASE表达式里面使用了聚合函数,又在聚合函数里面使用了CASE表达式。其主要目的是用CASE WHEN COUNT(*) =1.......ELSE.......来表示“只加入了一个社团还是加入了多个社团”。
我们一般对聚合结果进行条件判断时要用HAVING子句,但是在这个例子中可以看到,在SELECT语句里面使用CASE表达式也可以完成同样的工作。
对应于之前的,新手用WHERE子句进行分支,高手用SELECT子句进行条件分支。
我们这条的技巧可以总结为:新手用HAVING子句进行条件分支,高手用SELECT子句进行条件分支。
CASE表达式用在SELECT子句中时,既可以写在聚合函数内部,也可以写在聚合函数外部。