20 求一个SQL查询
数据如下
表
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
-----------------------------
1a10
2b9
3a8
4b6
5c15
6a5
7d10
8c12
9d8
10e7
11f5
12e9
13f17
14f17
15c15
*/
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
-----------------------------
14f17
15c15
7d10
1a10
12e9
2b9
*/
drop table tt
