--student info table
create table S ( S# int not NULL, SN varchar(20), SD varchar(20) ); --course table infoma create table C( C# int not null, CN varchar(50), ); --relation table create table SC( S# int not null, C# int not null, Score int default 0 ); insert into S values(1,'张三','计算机'); insert into S values(2,'李四','软件'); insert into S values(3,'王五','数据库'); insert into S values(4,'赵六','计算机'); insert into C values(1,'课程1'); insert into C values(2,'课程2'); insert into C values(3,'课程3'); insert into C values(4,'课程4'); insert into C values(5,'课程5'); insert into C values(6,'课程6'); --delete from sc insert into SC values(1,1,60); insert into SC values(1,2,20); insert into SC values(1,3,60); insert into SC values(1,4,80); insert into SC values(1,5,60); insert into SC values(1,6,60); insert into SC values(2,1,60); insert into SC values(2,2,60); insert into SC values(2,3,40); insert into SC values(2,4,30); insert into SC values(2,5,60); insert into SC values(3,3,60); --1.查询出 学了 ‘课程3 ’ 的 学生 --1.1 SELECT * FROM S WHERE S# IN ( SELECT S# FROM SC WHERE C#=(SELECT C# FROM C WHERE CN='课程3') ) --1.2 select S.* from S,C,SC where S.S#=SC.S# and SC.C#=C.C# and C.CN='课程3'; --1.3 select * from S; select * from SC; select * from C; select s.* from s inner join sc on s.s#=sc.s# inner join c on sc.c#=c.c# and c.cn='课程3' option(force order) select s.* from s inner join sc on s.s#=sc.s# inner join c on sc.c#=c.c# and c.cn='课程3' /*select s.* from sc inner join c on sc.c#=c.c# --and c.cn='课程3'; inner join s on s.s#=sc.s#*/ --1.4 Select S# , SN , SD From S Where Exists(Select 1 From SC Inner Join C On C.C#=SC.C# Where SC.S#=S.S# ) --2.查询学了所有课程的学生 --2.1 use d5 SELECT * FROM S INNER JOIN (SELECT S#,COUNT(1) _SUM FROM SC GROUP BY S#) AS T1 ON S.S#=T1.S# AND T1._SUM = (SELECT COUNT(1) FROM C) --2.2 select count(C#) as num from C ; --MySql --select S1 ,count(C1) as num from SC group by S1 having num=(select count(C1) from C); select * from S inner join (select S# ,count(C#) as num from SC group by S# having count(C#)=(select count(C#) from C)) as t1 on S.S#= t1.S#; --2.3最优 Select S.S# , S.SN , S.SD From S Where Not Exists(Select 1 From C Where Not Exists(Select 1 From SC Where SC.C#=C.C# And S#=S.S# ) ) --3.查询学了5门以上课程的学生 --3 --最优 Select S.S# , S.SN , S.SD From S Inner Join SC On SC.S#=S.S# Group By S.S# ,S.SN , S.SD Having Count(1)>=5 --mysql Select S.S1 , S.SN , S.SD From S Inner Join SC On SC.S1=S.S1 Group By S.S1 Having Count(1)>=5 --2.查询有两门以上不及格的成绩的学生,学好，姓名， -- <60的学生 select * from sc select S# ,C#, Score,count(S#) ss from SC group by S#,C# ,Score having Score<60 --注意count(S#) 统计的是 group by S#,C# 后的信息,只显示一行 SELECT S.S# ,S.SN FROM S , ( select S# ,C#,Score ss from SC group by S#,C# ,Score having Score<60 ) AS T1 where S.S#= T1.S# group by S.S#,S.SN having count(ss)>=2 --group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面 --mysql select S1 ,C1,Score from SC where Score<60 group by S1,C1 ,Score having count(S1)>=2 select S.S#,S.SN,S.SD from S inner join SC On SC.S#=S.S# group by S.S# having count(SC.C#) --3.如果让你建索引,你会在哪建 --4.统计一个学生成绩报表的思路 --表报格式如下 --学号 课程1 课程2 课程3 ... --001 100 100 100 --002 100 100 100 --... 【 学号和课程都可变 】 --method 1 固定死的，如果需求一变动，就需要改代码。 select SC.S# as 学号 , S.SN as 姓名, max(case when SC.C#=1 then SC.Score end ) as 课程1, max(case when SC.C#=2 then SC.Score end) as 课程2, max(case when SC.C#=3 then SC.Score end) as 课程3, max(case when SC.C#=4 then SC.Score end) as 课程4 from SC,S where SC.S#=S.S# group by SC.s#,S.SN --method 2 DECLARE @sql varchar(8000) set @sql='' select @sql=@sql+',max(case when sc.c#='''+Convert(varchar,sc.C#)+''' then sc.score end) ['+Convert(varchar,c.cn)+']' from sc,c WHERE sc.C# = c.c# group by sc.c#,c.cn set @sql=stuff(@sql,1,1,'') PRINT @sql exec ('select max(s.sn) as [Name],'+@sql+' from sc,s where sc.s# = s.s# group by sc.s#') DECLARE @s varchar(8000) set @s='' select @s=@s+',max(case when sc.c#='+Convert(varchar,sc.C#)+' then sc.score end) as '+convert(varchar,c.cn) from sc,c where sc.c#=c.c# group by sc.c#,c.cn set @s=stuff(@s,1,1,'') PRINT @s exec('select max(s.sn) as name ,'+@s+' from sc,s where sc.s#=s.s# group by sc.s#')