# Student(S#,Sname,Sage,Ssex) 学生表
# Course(C#,Cname,T#) 课程表
# SC(S#,C#,score) 成绩表
# Teacher(T#,Tname) 教师表
求一sql:
查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名?
# Course(C#,Cname,T#) 课程表
# SC(S#,C#,score) 成绩表
# Teacher(T#,Tname) 教师表
求一sql:
查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名?
where c.c# in
(
select c.c# from Student as s,course as c,sc
where s.s#=sc.s#,c.c#=sc.c#
and s.s#='1002'
)
where s.s#=sc.s#,c.c#=sc.c#
from student s join sc k on s.s#=k.s#
where s.s#<>'1002' and
not exists(select * from sc where s#='1002' and k.c#<>c# )
from student s join sc k on s.s#=k.s#
where s.s#<>'1002' and
not exists(select * from sc where s#='1002' and k.c#=c# )修改下~
select S#,sname
from Student
where S# in
(
select b.S# from SC a join SC b
on a.C#=b.C# and a.S#<>b.S# and a.S#='1002'
group by a.S#,b.S#
having count(1)=(select count(1) from SC where S#='1002')
)
declare @Student table (S# int,Sname varchar(2),Sage int,Ssex int)
insert into @Student
select 1001,'aa',23,1 union all
select 1002,'bb',24,0 union all
select 1003,'cc',25,1 union all
select 1004,'dd',21,1 union all
select 1005,'ee',22,0 union all
select 1006,'ff',23,0/*
模拟数据
3、5和2选课一样
6少选一个
4多选一个
1选的不一样
*/
declare @SC table (S# varchar(6),C# varchar(3),score varchar(3))
insert into @SC
select 1001,'001','89' union all
select 1001,'002','78' union all
select 1002,'001','87' union all
select 1002,'003','81' union all
select 1003,'001','90' union all
select 1003,'003','01' union all
select 1004,'001','69' union all
select 1004,'003','83' union all
select 1004,'004','78' union all
select 1005,'001','92' union all
select 1005,'003','100' union all
select 1006,'003','71'
declare @count int
select @count=COUNT(*) from @SC where S#='1002';with maco as (
select aa.C#,bb.S# from
(
select C# from @SC where S#='1002'
)aa right join @SC
bb on aa.C#=bb.C#) select dd.S#,dd.Sname from (
select S#,count(C#) as [count] from maco where S#
not in (select S# from maco where C# is null)
and S#<>'1002' group by S# )
cc left join @Student dd on cc.S#=dd.S#
where cc.[count]=@count/*
S# Sname
----------- -----
1003 cc
1005 ee
*/
SELECT S# FROM SC WHERE C# in (SELECT C# FROM SC WHERE S#='1002')
GROUP BY S# having count(*)=(SELECT count(*) FROM SC WHERE S#='1002');
一句话就是这样
insert into @Student
select 1001,'aa',23,1 union all
select 1002,'bb',24,0 union all
select 1003,'cc',25,1 union all
select 1004,'dd',21,1 union all
select 1005,'ee',22,0 union all
select 1006,'ff',23,0/*
模拟数据
3、5和2选课一样
6少选一个
4多选一个
1选的不一样
*/
declare @SC table (S# varchar(6),C# varchar(3),score varchar(3))
insert into @SC
select '1001','001','89' union all
select '1001','002','78' union all
select '1002','001','87' union all
select '1002','003','81' union all
select '1003','001','90' union all
select '1003','003','01' union all
select '1004','001','69' union all
select '1004','003','83' union all
select '1004','004','78' union all
select '1005','001','92' union all
select '1005','003','100' union all
select '1006','003','71'
select s.s# ,sname
from @Student s join @SC k on s.s#=k.s#
where s.s#<>'1002' and
not exists(select * from @SC where s#='1002' and k.c#=c# ) /*
s# sname
----------- -----
1001 aa
1004 dd
*/--小麦 3楼结果是这样的
insert into #Student
select 1001,'aa',23,1 union all
select 1002,'bb',24,0 union all
select 1003,'cc',25,1 union all
select 1004,'dd',21,1 union all
select 1005,'ee',22,0 union all
select 1006,'ff',23,0/*
模拟数据
3、5和2选课一样
6少选一个
4多选一个
1选的不一样
*/
create table #SC(S# varchar(6),C# varchar(3),score varchar(3))
insert into #SC
select '1001','001','89' union all
select '1001','002','78' union all
select '1002','001','87' union all
select '1002','003','81' union all
select '1003','001','90' union all
select '1003','003','01' union all
select '1004','001','69' union all
select '1004','003','83' union all
select '1004','004','78' union all
select '1005','001','92' union all
select '1005','003','100' union all
select '1006','003','71'select S#,Sname from #Student where S#
in(
select aa.S# from
(select S# from #SC
group by S# having count(*)=(select count(*) from #SC where S#='1002'))
aa
inner join
(select S# from #SC where C# in (select C# from #SC where S#='1002')
group by S# having count(*)=(select count(*) from #SC where S#='1002')
)bb
on aa.S#=bb.S# and aa.S#<>'1002')
--删除使用的表
drop table #Student
drop table #SC
/*
S# Sname
----------- -----
1003 cc
1005 ee
*/
(
select s# from @sc
where c# in
(select c# from @sc where s#='1002')
group by s#
having count(*)=(select count(*) from @sc where s#='1002')
) a,
(
select s# from @sc
group by s#
having count(*)=(select count(*) from @sc where s#='1002')
) b,
@student c
where a.s#=b.s# and a.s#=c.s# and a.s#<>'1002'
select stuid,Sname from t_student where stuid
in(
select aa.s_id from
(
select s_id from t_sc
group by s_id having count(*)=(select count(*) from t_sc where s_id='001')
--计算和001号学生上课课程数目相等的学生
) aa
inner join
(
select s_id from t_sc where c_id in(
select c_id from t_sc where s_id='001'
) --学了001号学生所学的任何一门课程的学生
group by s_id having count(*)=(select count(*) from t_sc where s_id='001')
--并且与001号学生学的课程个数与001学生所学课程数目一致的学生
)bb on aa.s_id=bb.s_id and aa.s_id<>'001'
)
--首先是课程数目相同的
select aa.S# from
(select S# from #SC
group by S# having count(*)=(select count(*) from #SC where S#='1002'))
aa
inner join
--然后是课程内容相同的
(select S# from #SC where C# in (select C# from #SC where S#='1002')
group by S# having count(*)=(select count(*) from #SC where S#='1002')
)bb
on aa.S#=bb.S# and aa.S#<>'1002'--然后两者求交集,去掉自身就是结果了。