求SQL语句，面试是没有做出来
查询学号==1的同学学习的课程完全相同的其他同学学号和姓名
表结构如下：
Student(sid,Sname) 学生表
Course(cid,Cname) 课程表
SC(sid,cid,score) 成绩表
[解决办法]
go
if OBJECT_ID('student')is not null
drop table tbl
go
create table student(
stuid int,
stuname varchar(10)
)
go
insert student
select 1,'tracy' union all
select 2,'tom' union all
select 3,'lucy' union all
select 4,'cate' union all
select 5,'lily'
go
if OBJECT_ID('course') is not null
drop table course
go
create table Course(
cid int,
cname varchar(10)
)
go
insert course
select 1,'javaee' union all
select 2,'c++' union all
select 3,'java' union all
select 4,'jsp' union all
select 5,'SQL Server'
go
if OBJECT_ID('SC') is not null
drop table SC
go
create table SC(
stuid int,
cid int,
score int
)
go
insert SC
select 1,2,68 union all
select 1,3,68 union all
select 1,5,68 union all
select 2,2,68 union all
select 2,4,68 union all
select 3,2,68 union all
select 3,3,68 union all
select 3,5,68 union all
select 4,1,68 union all
select 5,1,68 union all
select 5,2,68
select a.stuid,b.stuname from(
select stuid from SC where cid in(select cid from SC
where stuid=1) and stuid<>1
group by stuid having COUNT(*)=(select
COUNT(cid) from SC where stuid=1))a
inner join student b on a.stuid=b.stuid
/*
stuidstuname
3lucy
*/
[解决办法]
select a.stuid,b.stuname from(select stuid from SC where exists( select 1 from SC where stuid=1) and stuid<>1group by stuid having COUNT(*)=(select COUNT(cid) from SC where stuid=1))ainner join student b on a.stuid=b.stuid/*stuid stuname3 lucy*/
[解决办法]
declare @sql nvarchar(max)set @sql=';with t as(select stuid,stuname,'select @sql=@sql+' max(case cname when '''+isnull(cname,'')+''' then ''1'' else ''0'' end)+' from (select distinct cname from student inner join SCon student.stuid=SC.stuid inner join courseon course.cid=SC.cid)tselect @sql=substring(@sql,1,len(@sql)-1)select @sql=@sql+'as course from (select student.stuid,student.stuname,cname from student inner join SCon student.stuid=SC.stuid inner join courseon course.cid=SC.cid) tgroup by stuid,stuname)select t1.stuname from t left join t t1 on t.course=t1.courseand t.stuname<>t1.stunamewhere t.stuid=1'exec( @sql)
[解决办法]
select t.sid,t.snamefrom student twhere t.sid<>1 and not exists((select b.cid from student a inner join sc b on a.sid=b.sid and a.sid=1) except(select b.cid from student a inner join sc b on a.sid=b.sid and a.sid=t.sid))
[解决办法]
;with t as (select student.stuid,student.stuname,SC.cid,b.CNT from student inner join SCon student.stuid=SC.stuid inner join (select stuid,count(*) as CNT from SC group by stuid) bon b.stuid=student.stuid)select t1.stuid,t1.stuname from t left join t t1on t.cid=t1.cidand t.stuid<>t1.stuidwhere t.stuid=1and t1.CNT=t.CNTgroup by t1.stuid,t1.stunamehaving count(1)=avg(t.CNT)
[解决办法]
http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
--方法1
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '01' and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '02') order by Student.S#
--方法2
select Student.* from Student , SC where Student.S# = SC.S# and SC.C# = '02' and exists (Select 1 from SC SC_2 where SC_2.S# = SC.S# and SC_2.C# = '01') order by Student.S#
--方法3
select m.* from Student m where S# in
(
select S# from
(
select distinct S# from SC where C# = '01'
union all
select distinct S# from SC where C# = '02'
) t group by S# having count(1) = 2
)
order by m.S#
--11、查询没有学全所有课程的同学的信息
--11.1、
select Student.*
from Student , SC
where Student.S# = SC.S#
group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) < (select count(C#) from Course)
--11.2
select Student.*
from Student left join SC
on Student.S# = SC.S#
group by Student.S# , Student.Sname , Student.Sage , Student.Ssex having count(C#) < (select count(C#) from Course)
--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 student.* from student where not exists
(
select * from course where not exists
(
select * from sc where s# = student.s# and c# = course.c#
)
)
[解决办法]
--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select Student.* from Student where S# in
(select distinct SC.S# from SC where S# <> '01' and SC.C# in (select distinct C# from SC where S# = '01')
group by SC.S# having count(1) = (select count(1) from SC where S#='01'))
[解决办法]
create table student(sid int,name varchar(10))goinsert studentselect 1,'tracy' union allselect 2,'tom' union allselect 3,'lucy' union allselect 4,'cate' union allselect 5,'lily'gogoif OBJECT_ID('SC') is not nulldrop table SCgocreate table SC(sid int,cid int,score int)goinsert SCselect 1,2,68 union allselect 1,3,68 union allselect 1,5,68 union allselect 2,2,68 union allselect 2,4,68 union allselect 2,5,68 union allselect 3,2,68 union allselect 3,3,68 union allselect 3,4,68 union allselect 3,5,68 union allselect 4,1,68 union allselect 5,2,68 union allselect 5,3,68 union allselect 5,5,68 ---第一种方法select sid,namefrom (select a.sid,a.name,b.cid from Student a,sc b where a.sid=b.sid and b.cid in(select cid from SC where sid=1)) awhere (select count(*) from sc where sid=a.sid)=(select count(1) from SC where sid=1)and sid<>1 group by sid,name having count(*)=(select count(1) from SC where sid=1)