大侠们帮帮忙,看似简单的多对多查询到底该怎么写?
SidSname
001小王
002小李
003小张
004小刘
TidTname
001数学
002语文
003英语
SidTid
001001
001002
001003
002001
002002
003003
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 sid not in (select distinct sid from studentclass)
--5
select b.sname,count(*) as '科目数 '
from studentclass a left join student b on a.sid=b.sid left join class c on
a.tid=c.tid
group by sname
order by count(*) desc
[解决办法]
1.查询选修了所有课程的学生名?
select * from s_s where sid in (select sid from s_t where tid in(select tid from t_t ) group by sid having count(1)=(select count(1) from t_t))
2.查询没有选修李四所选课程的学生名?
select a.* from s_s a,s_t b where a.sid=b.sid and a.sid not in (select sid from s_t where tid in(
select tid from s_s a,s_t b where a.sid=b.sid and a.sname= '小李 ' ))
[解决办法]
3.查询选修了张三选修所有课程的学生名?
select a.* from s_s a,s_t b where a.sid=b.sid and a.sname <> '小张 ' and tid in(select tid from s_s a,s_t b where a.sid=b.sid and sname= '小张 ' )
[解决办法]
4.查询一门都没有选修的学生名?
select * from s_s where sid not in (select distinct a.sid from s_s a,s_t b where a.sid=b.sid )
