是要用到group by 的吧？
------解决方案--------------------
对，要用到group by的
------解决方案--------------------
当然也可以不用group by，但要用分组排序取每组第一个，完全看自己用哪种方式去写
------解决方案--------------------
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 tempdb
create 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','英语',59
select 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)
)
GO
CREATE TABLE Score
(
[No] NVARCHAR(100),
[subject] NVARCHAR(64),
[score] DECIMAL(18)
)
GO
INSERT 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
GO
INSERT INTO Student([No],[name])
SELECT '01','张三' UNION
SELECT '02','李四' UNION
SELECT '03','王五'
GO
SELECT * FROM Student
SELECT * 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