数据如下
表
id name hits
1 a 10
2 b 9
3 a 8
4 b 6
5 c 15
6 a 5
7 d 10
8 c 12
9 d 8
10 e 7
11 f 5
12 e 9
13 f 17
14 f 17
15 c 15
我要得到的结果是
id name hits
14 f 17
15 c 15
7 d 10
1 a 10
12 e 9
根据hits倒序得到数据，然后过滤重复出现的name和hits的数据，取hits最高的数据，如果排序下来，hits相同就按id倒序
分不在高，肯定有技术研究。。。
------解决方案--------------------
select * from tablename a
where id= (select top 1 id from tablename where name=a.name order by hits desc,id desc)
order by a.hits desc,id desc
------解决方案--------------------
Select A.* From 表 A Where id = (Select TOP 1 id From 表 Where name = A.name Order By hits Desc, id Desc)
------解决方案--------------------
create table tt ([id] int, [name] varchar(20),hits int)
insert into tt([id],[name], hits)
select 1, 'a ',10
union all
select 2, 'b ',9
union all
select 3, 'a ',8
union all
select 4, 'b ',6
union all
select 5, 'c ',15
union all
select 6, 'a ',5
union all
select 7, 'd ',10
union all
select 8, 'c ',12
union all
select 9, 'd ',8
union all
select 10 , 'e ',7
union all
select 11 , 'f ',5
union all
select 12 , 'e ',9
union all
select 13 , 'f ',17
union all
select 14 , 'f ',17
union all
select 15 , 'c ',15
select * from tt
/*
id name hits
-----------------------------
1 a 10
2 b 9
3 a 8
4 b 6
5 c 15
6 a 5
7 d 10
8 c 12
9 d 8
10 e 7
11 f 5
12 e 9
13 f 17
14 f 17
15 c 15
*/
select max(b.id) as id,a.name,a.hits from
(select name,max(hits) as hits from tt group by name) a
left join tt b on a.name=b.name and a.hits=b.hits
group by a.[name],a.hits
order by a.hits desc,max(b.id) desc
/*
id name hits
-----------------------------
14 f 17
15 c 15