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
