Question

急救！SQL server 三表连查 求高手指教

student表：stuId(主键),stuName,className
project表：proId(主键),proName
marks表:id(主键),stuId(外键),proId(外键),score
要求 查询出 每个班级 每个科目 成绩最好学员 的 分数
查询结果如：
stuName className project score
张三 高一(1)班 语文 89
李四 高一(1)班 数学 97
王五 高一(2)班 语文 91
赵六 高一(2)班 数学 95
....................................

Answer 1

方法1：
select a.stuName as stuName,a.className as className,b.proName as project,c.score
into #fk
from student a,project b,marks c
where a.stuId=c.stuId and b.proId=c.proId

select stuName,className,project,max(score) as score
from #fk
where stuName in (select stuName from #fk where score in(select max(score) from #fk group by className,project))
group by className,project,stuName

方法2：

with cte1 as(
select a.stuName as stuName,a.className as className,b.proName as project,max(c.score) as score
from marks c join student a on a.stuId=c.stuId
join project b on b.proId=c.proId
group by a.className,b.proName,a.stuName)
select stuName,className,project,max(score) as score
from cte1
where stuName in (select stuName from cte1 where score in(select max(score) from cte1 group by className,project))
group by className,project,stuName

Answer 2

select a.stuName,b.className,b.proName,b.maxscore
from student a
inner join
(select c.className,b.proId,b.proName,max(a.score) as maxscore
from marks a
inner join project b on a.proId=b.proId
inner join student c on a.stuId=c.stuId
group by c.className,b.proId,b.proName) b on a.className=b.className
inner join marks b on a.proId=b.proId and a.maxscore=b.score AND A.stuId=b.stuId
备注：直接复制到数据库执行就OK了

追问

a.className=b.className
inner join marks t on a.proId=b.proId and a.maxscore=t.score AND A.stuId=t.stuId

这段好像有点问题 你看下 我不是太懂。。。。

追答

不好意思顺序写错了
SELECT c.stuName,b.className,b.proName,b.maxscore
FROM marks a
INNER JOIN (
select c.className,b.proId,b.proName,max(a.score) as maxscore
from marks a
inner join project b on a.proId=b.proId
inner join student c on a.stuId=c.stuId
group by c.className,b.proId,b.proName
) b on a.proId=b.proId and b.maxscore=a.score
inner join student c on a.stuid=c.stuid and b.className=c.className
这个是没问题了 挂号里面表示某一科目的某一班的最高分
下面是我用临时表模拟的例子：
declare @student table(stuId int ,stuName Nvarchar(20),className Nvarchar(50))
declare @project table(proId int ,proName Nvarchar(20))
declare @marks table(id int identity(1,1),stuId int ,proId int,score money)
INSERT INTO @student VALUES(1,N'张三',N'高一(1)班')
INSERT INTO @student VALUES(2,N'李四',N'高一(1)班')
INSERT INTO @student VALUES(3,N'王五',N'高一(2)班')
INSERT INTO @student VALUES(4,N'赵六',N'高一(2)班')
INSERT INTO @project VALUES(1,N'语文')
INSERT INTO @project VALUES(2,N'数学')
INSERT @marks(stuId,proId,score) VALUES(1,1,89)
INSERT @marks(stuId,proId,score) VALUES(1,2,96)
INSERT @marks(stuId,proId,score) VALUES(2,1,79)
INSERT @marks(stuId,proId,score) VALUES(2,2,97)
INSERT @marks(stuId,proId,score) VALUES(3,1,91)
INSERT @marks(stuId,proId,score) VALUES(3,2,94)
INSERT @marks(stuId,proId,score) VALUES(4,1,88)
INSERT @marks(stuId,proId,score) VALUES(4,2,95)

SELECT c.stuName,b.className,b.proName,b.maxscore--,a.stuid
FROM @marks a
INNER JOIN (
select c.className,b.proId,b.proName,max(a.score) as maxscore
from @marks a
inner join @project b on a.proId=b.proId
inner join @student c on a.stuId=c.stuId
group by c.className,b.proId,b.proName
) b on a.proId=b.proId and b.maxscore=a.score
inner join @student c on a.stuid=c.stuid and b.className=c.className

Answer 3

select t.classname,t.project,t.stuName,max(t.store)
(select a.stuName,a.className,b.proName,c.score
from marks c join student a on a.stuId=c.stuId
join project b on b.proId=c.proId ) t group by t.className,t.project

应该这样了 试一试 不行再改改

Answer 4

select stuName,className,proName,max(score) from student s,project p,marks m where s.stuId=s.stuId and p.proId=m.proId group by s.stuName,s.className,p.proName