SQL Server中,group by 一般是和聚合函数一起搭配使用的,不然用了也没什么意
义除了消除重复功能外,但消除重复一般使用distinct。
例如,有这样的一学生成绩表(学号,课程号,成绩)
我们按学号分组查询,
select SNo from sc group by SNo
结果:
从上面我们可以看到,group by 子句可以将查询结果按某一列或多列的值进行分
组。
容易出错的点:
(1) 出现在select后面的字段 要么是是聚合函数中的,要么是在group by 中的。
什么意思?
例如:还是上面那个表,当我执行以下语句:
select sno,cno from sc group by sno
报错:(可能mySQL不会,因为它的执行是不严格的)
因为,我的select字段中有cno课程号,但却不是在聚合函数或者group by子句
中,所以报错。
正确用法:
//这一句是查看,每个人修读了几门课
select sno,count(cno) from sc group by sno
//这里先按学号分组再按课程号分组,只按学号分组不会出现重复的学号的
select sno,cno from sc group by SNo,CNo
(2) 如果是筛选结果 可以先使用where指定条件 再用group by 或者先用group
by 再用having来指定条件,(聚合函数不能出现在where子句中,只可以和select
或having搭配,SQL Server会报错,据说MySQL不会报错)
//查询选修两个门课及以上的学生
//报错
select SNo from sc group by SNo where count(*)>1
//正解
select SNo from sc group by SNo having count(*)>1
//查看每个学生选了几门课,且选的课的成绩不为空
//报错,group by要在where后面
select sno,count(cno) from sc group by SNo where score is not null
//正解
select sno,count(cno) from sc where score is not null group by SNo
总结
之前在面试试题看到这样一道题,有一个学生成绩表,就像
我们上面说的那个表一样,查询学生成绩重复的行。
确认过眼神,以为很简单,上来就是
select Score from sc group by Score having count(*)>1
and score is not null
毫无疑问,当然是错的,一开始没看到是查询行,也就是查询的
要包括整个元组的信息。想了想改成:
select * from sc group by Score having count(*)>1 and
score is not null
也是错的,犯了上面的问题。想了想,无奈,用内连接。
select a1.SNo,a1.CNo,a1.Score from sc a1
inner join sc on sc.Score=a1.Score and (a1.SNo!=sc.SNo
or a1.cno !=sc.CNo)
虽然结果对了,但是个人感觉要是做项目这样查,领导和同事得
用刀砍死我,因为耗费资源,效率还慢。
哎,简洁写法
select * from sc where score in(
select Score from sc group by Score having count(*)>1
and score is not null
)
这种东西还是得多练,刚上手时更是得练,如果感觉上手了
就搁置一段时间,用到时又得回去翻笔记。写下来,希望和各位
道友共勉。