多表查查查查

[yuanpeng_176]

学生表S
学号 Sno
姓名 Sname

课程信息表C
课程号 Cno
课程名 Cname

学生成绩表SC
学号 Sno
课程号 Cno
成绩 Score

查询每门课程的最高成绩、最低成绩的学生姓名及分数

[baiynije]

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

[q465897859]

 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  

[xiangaylian]

这个不能排除重复成绩的，还需要改一改的：

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

[koumingjie]

sql2005

create 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 行受影响)

[koumingjie]

sql2005

create 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 行受影响)

[程序猿GG]

;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

[pengxuan]

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)

[程序猿GG]

;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

[xiangaylian]

看似简单的问题，还花了一点时间：

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,43

with 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 
*/

[xiangaylian]

看似简单，还挺不好弄的，期待更好的答案：

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,43

with 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 
*/

[xiaoming5621163]

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

[xiaoming5621163]

确实是看似简单，做起来还有点麻烦

[--小F--]

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

看了就知道了

[q465897859]

[Quote=引用 2 楼 q465897859 的回复:]
SQL code

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
[/Quote]错了

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