sql查询语句 1题求解~！
name     subject         mark
aaa             1                 56
aaa             2               76
aaa             3                 76
aaa             4                 26
aaa             5                 16
bbb             1                 76
bbb             2                 56
bbb             3                 26
bbb             4                 58
bbb             5                 57

每人考5门课，求每门课的最高分的名字和分数  

CREATE   TABLE   [table1]   (
[name]   [char]   (10)   COLLATE   Chinese_PRC_CI_AS   NULL   ,
[subject]   [char]   (10)   COLLATE   Chinese_PRC_CI_AS   NULL   ,
[mark]   [int]   NULL  
)   ON   [PRIMARY]
GO
select   *   from   table1
insert   into   table1   values( 'aaa ', '1 ',56)
insert   into   table1   values( 'aaa ', '2 ',76)
insert   into   table1   values( 'aaa ', '3 ',76)
insert   into   table1   values( 'aaa ', '4 ',26)
insert   into   table1   values( 'aaa ', '5 ',16)
insert   into   table1   values( 'bbb ', '1 ',76)
insert   into   table1   values( 'bbb ', '2 ',56)
insert   into   table1   values( 'bbb ', '3 ',26)
insert   into   table1   values( 'bbb ', '4 ',58)
insert   into   table1   values( 'bbb ', '5 ',57)
这是脚本

------解决方案--------------------
select * from table1 as tmp
where not exists(select 1 from table1 where subject=tmp.subject and mark> tmp.mark)

------解决方案--------------------

--用楼上的代码，或

select *
from table1 as t
where mark =(select max(mark) from table1 where subject=T.subject)
order by subject

------解决方案--------------------
select t1.name,t1.mark
from table1 t1 inner join (select name,max(mark) 最高分 from table1 group by name) t2
on t1.name = t2.name and t1.mark = t2.最高分
order by t1.name