多表查查查查

Question

学生表S
学号 Sno  
姓名 Sname课程信息表C
课程号 Cno
课程名 Cname学生成绩表SC
学号 Sno
课程号 Cno
成绩 Score查询每门课程的最高成绩、最低成绩的学生姓名及分数

Answer 1

select sc.cno,sc.sno,s.sname,max(sc.score) as score 
from sc join s on sc.sno = s.sno 
group by sc.cno,sc.sno,s.sname
unoin all
select sc.cno,sc.sno,s.sname,min(sc.score) as score 
from sc join s on sc.sno = s.sno 
group by sc.cno,sc.sno,s.sname

Answer 2

 with cte as(
select sno,MAX(Score)as 最高分,MIN(Score) as 最低分
from SC
group by sno)
select S.Sname,cte.最高分,cte.最低分 from S  LEFT JOIN cte on s.sno=cte.sno  

Answer 3

这个不能排除重复成绩的，还需要改一改的：Create Table #S(sno int,sname nvarchar(10))
insert into #S(sno,sname)
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
Create Table #C(cno int,cname nvarchar(10))
insert into #C(cno,cname)
select 1,'语文' union all
select 2,'数学' union all
select 3,'英语'
Create Table #SC(sno int,cno int,score int)
insert into #SC(sno,cno,score)
select 1,1,55 union all
select 1,2,76 union all
select 1,3,80 union all
select 2,1,65 union all
select 2,2,66 union all
select 2,3,43 union all
select 3,1,60 union all
select 3,2,87 select cc.cname,
isnull((select sname from #S where sno = scc.sno) + ':','') + cast(cc.maxscore as nvarchar(5)) as [最高成绩],
isnull((select sname from #S where sno = sccc.sno) + ':','') + cast(cc.minscore as nvarchar(5)) as [最低成绩] 
from #SC scc right join 
(
select c.cno,c.cname,
max(sc.score) as maxscore,min(sc.score) as minscore from #SC sc inner join #C c on sc.cno = c.cno 
inner join #S s on sc.sno = s.sno group by c.cno,c.cname
) cc on scc.cno = cc.cno and cc.maxscore = scc.score
left join #SC sccc on sccc.cno = cc.cno and cc.minscore = sccc.score

Answer 4

sql2005create table S
(Sno int,    
Sname nvarchar(10))create table C
(Cno int,
 Cname nvarchar(10))create table SC
(Sno int, 
 Cno int,
 Score int)
 
 
 insert into S 
 select 1,'张三'  union all
select 2,'李四' union all
select 3,'王五'insert into C 
select 10,'语文' union all
select 20,'数学' union all
select 30,'英语' 
insert into SC
select 1,10,90 union all
select 1,20,89 union all
select 1,30,88 union all
select 2,10,80 union all
select 2,20,79 union all
select 2,30,78 union all
select 3,10,60 union all
select 3,20,66 union all
select 3,30,100 ;with cte as
(
 select *,rowNum=dense_rank() over(partition by Cno order by Score desc) from SC
)
select t1.Sno,t2.Sname,t1.Cno,t3.Cname,t1.Score from cte t1 
left join S t2 on t1.Sno=t2.Sno left join C t3 on t1.Cno=t3.Cno 
where t1.rowNum=1
Sno         Sname      Cno         Cname      Score
----------- ---------- ----------- ---------- -----------
1           张三         10          语文         90
1           张三         20          数学         89
3           王五         30          英语         100(3 行受影响)

Answer 5

sql2005create table S
(Sno int,    
Sname nvarchar(10))create table C
(Cno int,
 Cname nvarchar(10))create table SC
(Sno int, 
 Cno int,
 Score int)
 
 
 insert into S 
 select 1,'张三'  union all
select 2,'李四' union all
select 3,'王五'insert into C 
select 10,'语文' union all
select 20,'数学' union all
select 30,'英语' 
insert into SC
select 1,10,90 union all
select 1,20,89 union all
select 1,30,88 union all
select 2,10,80 union all
select 2,20,79 union all
select 2,30,78 union all
select 3,10,60 union all
select 3,20,66 union all
select 3,30,100 ;with cte as
(
 select *,rowNum=dense_rank() over(partition by Cno order by Score desc) from SC
)
select t1.Sno,t2.Sname,t1.Cno,t3.Cname,t1.Score from cte t1 
left join S t2 on t1.Sno=t2.Sno left join C t3 on t1.Cno=t3.Cno 
where t1.rowNum=1
Sno         Sname      Cno         Cname      Score
----------- ---------- ----------- ---------- -----------
1           张三         10          语文         90
1           张三         20          数学         89
3           王五         30          英语         100(3 行受影响)

Answer 6

;WITH T(Cno,MAX_SCORE,MIN_SCORE) AS
(
SELECT Cno,MAX(Score) AS MAX_SCORE,MIN(Score) AS MIN_SCORE
FROM SC
GROUP BY SC
)
SELECT C.Cname,'最高成绩' as [类别], S.*,T.MAX_SCORE
FROM T JOIN SC ON T.Cno=SC.Cno AND T.MAX_SCORE=SC.Score
JOIN S ON SC.Sno=S.Sno
JOIN C ON C.Cno=T.Cno
UNION ALL
SELECT C.Cname,'最低成绩' as [类别], S.*,T.MIN_SCORE
FROM T JOIN SC ON T.Cno=SC.Cno AND T.MIN_SCORE=SC.Score
JOIN S ON SC.Sno=S.Sno
JOIN C ON C.Cno=T.Cno
ORDER BY Cno, [类别] DESC

Answer 7

if object_id('S','U') is not null
   drop table S
go
create table S(Sno varchar(10),Sname varchar(10))
go
insert into S
select '001','张三' union all
select '002','李四' union all
select '003','王五' union all
select '004','赵六'
go
if object_id('C','U') is not null
   drop table C
go
create table C(Cno varchar(10),Cname varchar(10))
go
insert into C
select '01','语文' union all
select '02','数学'
go
if object_id('SC','U') is not null
   drop table SC
go
create table SC(Sno varchar(10),Cno varchar(10),Score int)
go
insert into SC
select '001','01',10 union all
select '001','02',20 union all
select '002','01',30 union all
select '002','02',40 union all
select '003','01',50 union all
select '003','02',60 union all
select '004','01',70 union all
select '004','02',80
go
select 
  c.Cname,
  b.Sname,
  '最高成绩',
  Score
from SC a inner join S b on a.Sno=b.Sno inner join C c on a.Cno=c.Cno
where not exists(select 1 from SC where Cno=a.Cno and Score>a.Score)
union all
select 
  c.Cname,
  b.Sname,
  '最低成绩',
  Score
from SC a inner join S b on a.Sno=b.Sno inner join C c on a.Cno=c.Cno
where not exists(select 1 from SC where Cno=a.Cno and Score<a.Score)

Answer 8

;WITH T(Cno,MAX_SCORE,MIN_SCORE) AS
(
SELECT Cno,MAX(Score) AS MAX_SCORE,MIN(Score) AS MIN_SCORE
FROM SC
GROUP BY SC
)
SELECT C.Cname,'最高成绩' as [类别], S.*,T.MAX_SCORE
FROM T JOIN SC ON T.Cno=SC.Cno AND T.MAX_SCORE=SC.Score
JOIN S ON SC.Sno=S.Sno
JOIN C ON C.Cno=T.Cno
UNION ALL
SELECT C.Cname,'最低成绩' as [类别], S.*,T.MIN_SCORE
FROM T JOIN SC ON T.Cno=SC.Cno AND T.MIN_SCORE=SC.Score
JOIN S ON SC.Sno=S.Sno
JOIN C ON C.Cno=T.Cno
ORDER BY Cno, [类别] DESC

Answer 9

看似简单的问题，还花了一点时间：Create Table #S(sno int,sname nvarchar(10))
insert into #S(sno,sname)
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
insert into #S(sno,sname)
select 4,'赵六'Create Table #C(cno int,cname nvarchar(10))
insert into #C(cno,cname)
select 1,'语文' union all
select 2,'数学' union all
select 3,'英语'
Create Table #SC(sno int,cno int,score int)
insert into #SC(sno,cno,score)
select 1,1,55 union all
select 1,2,76 union all
select 1,3,80 union all
select 2,1,65 union all
select 2,2,66 union all
select 2,3,43 union all
select 3,1,60 union all
select 3,2,87 
insert into #SC(sno,cno,score)
select 4,1,65 union all
select 4,2,87 union all
select 4,3,43with cc as
(
select c.cno,c.cname,max(sc.score) as maxscore,min(sc.score) as minscore 
from #SC sc 
inner join #C c on sc.cno = c.cno 
inner join #S s on sc.sno = s.sno 
group by c.cno,c.cname
)
select distinct cc.cname,
isnull((select sname + ':' + cast(cc.maxscore as nvarchar(5)) + ' ' from #S inner join #SC on #S.sno = #SC.sno where #SC.cno = cc.cno and #SC.score = cc.maxscore for xml path('')),'')  as [最高成绩],
isnull((select sname + ':' + cast(cc.minscore as nvarchar(5)) + ' ' from #S inner join #SC on #S.sno = #SC.sno where #SC.cno = cc.cno and #SC.score = cc.minscore for xml path('')),'')  as [最低成绩]
from #SC scc 
right join cc on scc.cno = cc.cno and cc.maxscore = scc.score
left join #SC sccc on sccc.cno = cc.cno and cc.minscore = sccc.score
/*结果：
cname 最高成绩            最低成绩
数学    王五:87 赵六:87      李四:66 
英语    张三:80              李四:43 赵六:43 
语文    李四:65 赵六:65      张三:55 
*/

Answer 10

看似简单，还挺不好弄的，期待更好的答案：Create Table #S(sno int,sname nvarchar(10))
insert into #S(sno,sname)
select 1,'张三' union all
select 2,'李四' union all
select 3,'王五'
insert into #S(sno,sname)
select 4,'赵六'Create Table #C(cno int,cname nvarchar(10))
insert into #C(cno,cname)
select 1,'语文' union all
select 2,'数学' union all
select 3,'英语'
Create Table #SC(sno int,cno int,score int)
insert into #SC(sno,cno,score)
select 1,1,55 union all
select 1,2,76 union all
select 1,3,80 union all
select 2,1,65 union all
select 2,2,66 union all
select 2,3,43 union all
select 3,1,60 union all
select 3,2,87 
insert into #SC(sno,cno,score)
select 4,1,65 union all
select 4,2,87 union all
select 4,3,43with cc as
(
select c.cno,c.cname,max(sc.score) as maxscore,min(sc.score) as minscore 
from #SC sc 
inner join #C c on sc.cno = c.cno 
inner join #S s on sc.sno = s.sno 
group by c.cno,c.cname
)
select distinct cc.cname,
isnull((select sname + ':' + cast(cc.maxscore as nvarchar(5)) + ' ' from #S inner join #SC on #S.sno = #SC.sno where #SC.cno = cc.cno and #SC.score = cc.maxscore for xml path('')),'')  as [最高成绩],
isnull((select sname + ':' + cast(cc.minscore as nvarchar(5)) + ' ' from #S inner join #SC on #S.sno = #SC.sno where #SC.cno = cc.cno and #SC.score = cc.minscore for xml path('')),'')  as [最低成绩]
from #SC scc 
right join cc on scc.cno = cc.cno and cc.maxscore = scc.score
left join #SC sccc on sccc.cno = cc.cno and cc.minscore = sccc.score/*结果：
cname 最高成绩            最低成绩
数学    王五:87 赵六:87     李四:66 
英语    张三:80             李四:43 赵六:43 
语文    李四:65 赵六:65     张三:55 
*/

Answer 11

select Sname1,max(score),Sname2,min(score)
from S,C,SC
where S_Sno=SC_Sno,C_Cno=SC_Cno
group by Sname

Answer 12

http://topic.csdn.net/u/20100517/17/b2ab9d5e-73a2-4f54-a7ec-40a5eabd8621.html?89116看了就知道了

Answer 13

错了 
select S.Sname,n.最高分,n.最低分 from S  LEFT JOIN sc on s.sno=sc.sno
left join (select cno,MAX(Score)as 最高分,MIN(Score) as 最低分
from SC
group by cno) n on sc.cno=n.cno