1)SELECT Sno,COUNT(Cno) FROM SC
GROUP BY Sno
HAVING COUNT(COUNT(Grade>90))>3;
2)SELEECT Sno,COUNT(Cno) FROM SC
WHERE Grade>90
GROUP BY Sno
HAVING COUNT(*)>3;在mysql中进行了验证,
1)提示以下错误信息:
ERROR 1111 (HY000):Invalid use of group function
2)Empty set(0.00 sec)
说明在SC表中,没有满足条件的元组谁能帮忙解释一下1)错误的原因?
GROUP BY Sno
HAVING COUNT(COUNT(Grade>90))>3;
2)SELEECT Sno,COUNT(Cno) FROM SC
WHERE Grade>90
GROUP BY Sno
HAVING COUNT(*)>3;在mysql中进行了验证,
1)提示以下错误信息:
ERROR 1111 (HY000):Invalid use of group function
2)Empty set(0.00 sec)
说明在SC表中,没有满足条件的元组谁能帮忙解释一下1)错误的原因?
where grade>90
GROUP BY Sno
HAVING COUNT(1)>3;
在WHERE的基础上,GROUP BY进行分组
在GROUP BY之后,HAVING对组再进行筛选
最后进行ORDER BY操作所以你的题目,应当就是3楼的答案SELECT Sno,COUNT(Cno) FROM SC
where grade>90 -- 只取所有分数 > 90的人
GROUP BY Sno -- 按照Sno分组
HAVING COUNT(1)>3; -- 只取组里面 > 90分条件的人数超过3的组,其余分组舍弃翻译过来,就是按照Sno分组,取出所有分数超过90分人数大于3人的组,及相应人数
having count(case when grade>90 then 1 end)>3