大侠们帮帮忙,看似简单的多对多查询到底该怎么写?
Sid Sname
001 小王
002 小李
003 小张
004 小刘

Tid Tname
001 数学
002 语文
003 英语

Sid Tid
001 001
001 002
001 003
002 001
002 002
003 003

1.查询选修了所有课程的学生名?
2.查询没有选修李四所选课程的学生名?
3.查询选修了张三选修所有课程的学生名?
4.查询一门都没有选修的学生名?
5.查询所有学生的编号,并且按照选课数目排序,选课最多的学生编号排在最前,并且给出每个学生的选课数目,没有选择课目的学生编号不在列表中出现?



------解决方案--------------------
设第一个表为:a 第二个表为:b 第三个表为:c
(1)select a.sid from a join c on(a.sid=c.sid) group by a.sid having count(*)=(select count(*) from b)
(2)select a.sname from a join c on(a.sid=c.sid) where c.tid not in
(select c.tid from a join c on(a.sid=c.sid) where a.sname= '李四 ') group by a.sname
(3)select aa.sname from (select a.sname,c.tid from a join c on(a.sid=c.sid) aa join ( select c.tid from a join c on(a.sid=c.sid) where a.sname= '张三 ') bb on (aa.tid=bb.tid) group by aa.sname having count(*)=(select count(*) from a join c on(a.sid=c.sid) where a.sname= '张三 ')
(4) select * from a where not exists(select * from c where c.sid=a.sid)
(5)select a.sname,count(*) from a join c on (a.sid=c.sid) group by a.sname order by 2
------解决方案--------------------
/*
SidSname
001小王
002小李
003小张
004小刘

TidTname
001数学
002语文
003英语

SidTid
001001
001002
001003
002001
002002
003003

1.查询选修了所有课程的学生名?
2.查询没有选修李四所选课程的学生名?
3.查询选修了张三选修所有课程的学生名?
4.查询一门都没有选修的学生名?
5.查询所有学生的编号,并且按照选课数目排序,选课最多的学生编号排在最前,并且给出每个学生的选课数目,没有选择课目的学生编号不在列表中出现?
*/

drop table student
drop table class
drop table studentclass

create table student (sid varchar(3),sname varchar(20))
create table class (tid varchar(3),tname varchar(20))
create table studentclass (sid varchar(3),tid varchar(3))

insert into student
select '001 ',N '小王 ' UNION ALL
SELECT '002 ',N '小李 ' union all
SELECT '003 ',N '小张 'union all
SELECT '004 ',N '小刘 '
Insert into class
select '001 ', '语文 ' union all
select '002 ', '数学 ' union all
select '003 ', '英语 '

insert into studentclass
select '001 ', '001 ' union all
select '001 ', '002 ' union all
select '001 ', '003 ' union all
select '002 ', '001 ' union all
select '002 ', '002 ' union all
select '003 ', '003 '

--1
select b.sname
from studentclass A LEFT join student b on a.sid=b.sid
left join class c on a.tid=c.tid
group by b.sname having count(tname)=(select count(*) from class)
--2
select * from student where sid not in
(select sid from studentclass where tid in
(select c.tid
from studentclass A LEFT join student b on a.sid=b.sid
left join class c on a.tid=c.tid where sname= '小李 ')
group by sid having count(*)=(select count(*) from studentclass where sid in (select sid from
student where sname= '小李 ')))
--3
select * from student where sid in
(select sid from studentclass where tid in
(select c.tid
from studentclass A LEFT join student b on a.sid=b.sid
left join class c on a.tid=c.tid where sname= '小张 ')
group by sid having count(*)=(select count(*) from studentclass where sid in (select sid from
student where sname= '小张 ')))
--4
select * from student where s