两个查询问题，想了几个小时都没有想出来，请大家给点意见
题目一：表A有 学生ID，课程ID，（都为主键）字段
表B有 课程ID字段（主键）
请问如何查询出 选了所有课程的学生ID呢？
题目二：表A中，选的课程最多的学生ID？
问题点数：100、回复次数：5Top
1 楼zhaoanle（zhao）回复于 2006-03-04 18:06:25 得分 50
一.
select 学生ID from 表A group by 学生ID having count(学生ID)=(select count(*) from 表B)
二.
select b.学生ID from
(select max(a.数量) as '数量' from (select 学生ID,count(学生ID) as '数量' from 表A group by 学生ID) a) c,
(select 学生ID,count(学生ID) as '数量' from 表A group by 学生ID) b
where c.数量=b.数量Top
2 楼wgsasd311（自强不息）回复于 2006-03-04 19:30:58 得分 30
--ta:学生表(stuid,sname)
--tb:选课表(stuid,cno,grade)
--tc:课程表(cno,cname
--1选了所有课程的学生ID及姓名
select stuid,sname from ta a
where not exists
(select 1 from tc c where not exists
(select * from tb b where a.stuid=b.stuid and c.cno=b.cno )
)
--or 或只取学生ID，通过两表
select distinct stuid
from tb a
where not exists
(
select 1 from tc b where not exists
(
select * from tb c where c.stuid=a.stuid and c.cno=b.cno
)
)
--2表A中，选的课程最多的学生ID及姓名
select stuid
from tb a
where
not exists
(select 1 from tb group by stuid
having count(1)>(select count(1) from tb where stuid=a.stuid) )Top
3 楼wgsasd311（自强不息）回复于 2006-03-04 19:34:03 得分 0
--ta:学生表(stuid,sname)
--tb:选课表(stuid,cno,grade)
--tc:课程表(cno,cname
--1选了所有课程的学生ID及姓名
select stuid,sname from ta a
where not exists
(select 1 from tc c where not exists
(select * from tb b where a.stuid=b.stuid and c.cno=b.cno )
)
--or 或只取学生ID，通过两表
select distinct stuid
from tb a
where not exists
(
select 1 from tc b where not exists
(
select * from tb c where c.stuid=a.stuid and c.cno=b.cno
)
)
--2表A中，选的课程最多的学生ID及姓名
select * from ta a
where not exists
(
select 1 from tb group by stuid
having count(1)>(select count(1) from tb where stuid=a.stuid)
)
--or 或只取学生ID，通过两表
select distinct stuid
from tb a
where
not exists
(select 1 from tb group by stuid
having count(1)>(select count(1) from tb where stuid=a.stuid) )Top
4 楼scmail81（琳·风の狼(修罗)）回复于 2006-03-04 20:31:53 得分 0
create table A
(
学生Id int,
课程Id int
)
insert A select 1,1
insert A select 1,2
insert A select 1,3
insert A select 1,4
insert A select 2,1
insert A select 2,2
insert A select 2,3
insert A select 3,1
insert A select 3,2
create table B
(
课程Id int
)
insert B select 1
insert B select 2
insert B select 3
insert B select 4
1.
select A.学生Id
from
(select 学生Id,count(*) as num from A group by 学生Id) A,
(select count(*) as num from B)B
where A.num=B.num
2.
select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC
or
select distinct * from (
select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC) TTop
5 楼scmail81（琳·风の狼(修罗)）回复于 2006-03-04 20:32:19 得分 20
2.
select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC
--or
select distinct * from (
select top 1 WiTh Ties T.学生ID from A T order by (select count(*) from A where T.学生ID=学生ID) DESC) TTop
