select * from tablea a where id =(select top 1 id from tablea where num=a.num order by score desc,id )
select a.* from TableA a where not exists(select 1 from TableA where num=a.num and score>a.score)
--建立测试环境 Create Table 表(id varchar(10),score int,num int) --插入数据 insert into 表 select '12',10,1 union select '13',20,1 union select '14',30,2 union select '15',40,2 union select '17',40,2 union select '16',50,3 select * from 表 --测试语句 1。若num相同有多条数据的score都是最大时取id最大的一条 select a.* from 表 a inner join (select max(id) id, max(score)score, num from 表 group by num)b on a.score = b.score and a.num = b.num and a.id = b.id
2.若num相同有多条数据的score都是最大时都取 select a.* from 表 a inner join (select max(score)score, num from 表 group by num)b on a.score = b.score and a.num = b.num --删除测试环境 Drop Table 表--结果 1。 id score num 13 20 1 16 50 3 17 40 2 2。 id score num 16 50 3 17 40 2 15 40 2 13 20 1
where id =(select top 1 id from tablea where num=a.num order by score desc,id
)
a.*
from
TableA a
where
not exists(select 1 from TableA where num=a.num and score>a.score)
Create Table 表(id varchar(10),score int,num int)
--插入数据
insert into 表
select '12',10,1 union
select '13',20,1 union
select '14',30,2 union
select '15',40,2 union
select '17',40,2 union
select '16',50,3
select * from 表
--测试语句
1。若num相同有多条数据的score都是最大时取id最大的一条
select a.* from 表 a inner join
(select max(id) id, max(score)score, num from 表 group by num)b
on a.score = b.score and a.num = b.num and a.id = b.id
2.若num相同有多条数据的score都是最大时都取
select a.* from 表 a inner join
(select max(score)score, num from 表 group by num)b
on a.score = b.score and a.num = b.num
--删除测试环境
Drop Table 表--结果
1。
id score num
13 20 1
16 50 3
17 40 2
2。
id score num
16 50 3
17 40 2
15 40 2
13 20 1