05以上可以用cte实现 with cte as ( select no,subject,score,row_number() over(partition by no,subject order by score desc) cnt from exam ) select cte.no,a.name,cte.subject,cte.score from cte join student a on cte.no=a.no where cnt=1
use tempdbcreate table student(NO varchar(10),name varchar(30)) insert into student select '01','张三' union all select '02','李四' union all select '03','王五' create table Exam (NO varchar(10),[subject] varchar(30),score int) insert into exam select '01','数学',68 union all select '01','语文',89 union all select '01','英语',32 union all select '02','数学',80 union all select '02','语文',69 union all select '02','英语',59select a.name,b.subject,b.score from student a join exam b on a.no=b.no where not exists(select 1 from exam where subject=b.subject and score>b.score)-----用group by select a.name,b.subject,b.score from student a join exam b on a.no=b.no join (select subject,max(score) as max_score from exam group by subject) c on b.subject=c.subject and b.score=c.max_score
CREATE TABLE Student ( [No] NVARCHAR(100) PRIMARY KEY, [name] NVARCHAR(64) ) GOCREATE TABLE Score ( [No] NVARCHAR(100), [subject] NVARCHAR(64), [score] DECIMAL(18) ) GOINSERT INTO Score([No],[subject],[score]) SELECT '01','数学',68 UNION ALL SELECT '01','语文',89 UNION ALL SELECT '01','英语',32 UNION ALL SELECT '02','数学',80 UNION ALL SELECT '02','语文',69 UNION ALL SELECT '02','英语',59 UNION ALL SELECT '03','数学',90 UNION ALL SELECT '03','语文',96 UNION ALL SELECT '03','英语',92 GOINSERT INTO Student([No],[name]) SELECT '01','张三' UNION SELECT '02','李四' UNION SELECT '03','王五' GO SELECT * FROM StudentSELECT * FROM Score GO--关联查询 SELECT stu.[No],stu.[name],scoGB.[subject],scoGB.MaxScore FROM (SELECT MAX(Score) MaxScore,[subject] FROM Score GROUP BY [subject]) scoGB LEFT JOIN [Score] sco ON (sco.score=scoGB.MaxScore) LEFT JOIN [Student] stu ON (stu.[No]=sco.[No]) GO
select s1. no, s1.name,e3.subject,e3.max from student s1, (select e1. no ,e2.subject,e2.max from exam e1,(select max(score)max ,subject from exam group by subject) e2 where e1.subject=e2.subject and e1.score=e2.max) e3 where s1.no=e3.no
with cte as
(
select no,subject,score,row_number() over(partition by no,subject order by score desc) cnt
from exam
)
select cte.no,a.name,cte.subject,cte.score from cte join student a on cte.no=a.no where cnt=1
use tempdbcreate table student(NO varchar(10),name varchar(30))
insert into student
select '01','张三' union all
select '02','李四' union all
select '03','王五' create table Exam (NO varchar(10),[subject] varchar(30),score int)
insert into exam
select '01','数学',68 union all
select '01','语文',89 union all
select '01','英语',32 union all
select '02','数学',80 union all
select '02','语文',69 union all
select '02','英语',59select a.name,b.subject,b.score
from student a
join exam b on a.no=b.no
where not exists(select 1 from exam where subject=b.subject and score>b.score)-----用group by
select a.name,b.subject,b.score
from student a
join exam b on a.no=b.no
join (select subject,max(score) as max_score from exam group by subject) c
on b.subject=c.subject and b.score=c.max_score
(
[No] NVARCHAR(100) PRIMARY KEY,
[name] NVARCHAR(64)
)
GOCREATE TABLE Score
(
[No] NVARCHAR(100),
[subject] NVARCHAR(64),
[score] DECIMAL(18)
)
GOINSERT INTO Score([No],[subject],[score])
SELECT '01','数学',68 UNION ALL
SELECT '01','语文',89 UNION ALL
SELECT '01','英语',32 UNION ALL
SELECT '02','数学',80 UNION ALL
SELECT '02','语文',69 UNION ALL
SELECT '02','英语',59 UNION ALL
SELECT '03','数学',90 UNION ALL
SELECT '03','语文',96 UNION ALL
SELECT '03','英语',92
GOINSERT INTO Student([No],[name])
SELECT '01','张三' UNION
SELECT '02','李四' UNION
SELECT '03','王五'
GO SELECT * FROM StudentSELECT * FROM Score
GO--关联查询
SELECT stu.[No],stu.[name],scoGB.[subject],scoGB.MaxScore FROM (SELECT MAX(Score) MaxScore,[subject] FROM Score GROUP BY [subject]) scoGB
LEFT JOIN [Score] sco ON (sco.score=scoGB.MaxScore)
LEFT JOIN [Student] stu ON (stu.[No]=sco.[No])
GO
from student s1,
(select e1. no ,e2.subject,e2.max from exam e1,(select max(score)max ,subject from exam group by subject) e2
where e1.subject=e2.subject and e1.score=e2.max) e3
where s1.no=e3.no