sql语句分析

[longai123]

4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
RIGHT JOIN C ON SC.[C#]=C.[C#]
GROUP BY [S#]
HAVING COUNT(*)=COUNT(DISTINCT [S#]))
后面的HAVING COUNT(*)=COUNT(DISTINCT [S#]))看不明白啊....

[longai123]

这个啊
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
RIGHT JOIN C ON SC.[C#]=C.[C#]
GROUP BY [S#]
HAVING COUNT(*)=COUNT(DISTINCT [S#]))

[勿勿]

SQL HAVING 子句

[needacoder]

HAVING COUNT(*)=COUNT(DISTINCT [S#])是对结果集进行限制，只让选修科目数和总科目数相等的出现在结果集中
其中的COUNT(DISTINCT [S#])就是查出科目总数的，由于有distinct所以科目不重复出现，自然就是总科目数了

[needacoder]

having的效果和where极相似，但是：
having关键字一般和group by分组关键字一起使用，用于分组统计，然后用having筛选条件符合的条目

[dawugui]

[Quote=引用楼主 longai123 的回复:]
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
SELECT SN,SD FROM S
WHERE [S#] IN(
SELECT [S#] FROM SC
RIGHT JOIN C ON SC.[C#]=C.[C#]
GROUP BY [S#]
HAVING COUNT(*)=COUNT(DISTINCT [S#]))
后面的HAVING COU……
[/Quote]
某(些)个学生课程的数量 = 课程的总数量.

http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html
一个项目涉及到的50个Sql语句

--45、查询选修了全部课程的学生信息
--方法1 根据数量来完成
select student.* from student where S# in
(select S# from sc group by S# having count(1) = (select count(1) from course))
--方法2 使用双重否定来完成
select t.* from student t where t.S# not in
(
select distinct m.S# from
(
select S# , C# from student , course
) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#)
)
--方法3 使用双重否定来完成
select t.* from student t where not exists(select 1 from
(
select distinct m.S# from
(
select S# , C# from student , course
) m where not exists (select 1 from sc n where n.S# = m.S# and n.C# = m.C#)
) k where k.S# = t.S#
)