select a.Badge from yourtable a,
(select Course,max(Grade) as Grade from yourtable group by Course)b
where a.Course=b.Course and a.Grade=b.Grade
(select Course,max(Grade) as Grade from yourtable group by Course)b
where a.Course=b.Course and a.Grade=b.Grade
(select Course,max(Grade) as Grade from yourtable group by Course)b
on a.Course=b.Course and a.Grade=b.Grade
(select Course max(Grade) as Grade from table group by course) b
where a.course=b.course and a.grade=b.grade
Grade=(select max(Grade) as Grade from 表 group by Course
where Course=a.Course and Grade=a.Grade)
from 表 a
declare @a table(TID int,Badge varchar(10),Course varchar(20),Grade int)insert @a
select 1,'10001','T-SQL',60
union all
select 456,'11045','Oracle',71
union all
select 2,'20460','Jave',34
union all
select 1,'10011','T-SQL',59
union all
select 23,'10001','C#',90
union all
select 7,'20001','Visual Basic',12
union all
select 89,'20078','MBA',76
union all
select 7,'20001','Visual Basic',78
union all
select 4,'30001','HR Manager',71
union all
select 5,'20048','ISO',36--查询语句
select * from @a
where (convert(varchar(10),grade) + Course)
in (
select (convert(varchar(10),max(grade)) + Course) from @a
group by Course)--结果
TID Badge Course Grade
----------- ---------- -------------------- -----------
1 10001 T-SQL 60
456 11045 Oracle 71
2 20460 Jave 34
23 10001 C# 90
89 20078 MBA 76
7 20001 Visual Basic 78
4 30001 HR Manager 71
5 20048 ISO 36(8 row(s) affected)
(select course,max(Grade)as Grade from table group by course) b
on a.course=b.course and a.grade=b.grade