30分一个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
我要得到的结果是
id         name     hits
13         f         17
5           c         15
7           d         10
1           a         10
12         e         9  
根据hits倒序得到数据，然后过滤重复出现的name取hits高的数据，如果排序下来，hits相同就按id倒序
分不在高，肯定有技术研究。。。


------解决方案--------------------
select *
from table01 a
where not exists(select 1 from table01 b where a.name =b.name and a.hits <b.hits)
order by hits desc ,name desc
------解决方案--------------------
declare @t table(id int, name varchar(10), hits int)
insert @t
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

----方法1
select * from @t as a where not exists(select 1 from @t where name = a.name and hits > a.hits)
order by hits DESC,id DESC
----方法2
select * from @t as a where hits = (select max(hits) from @t where name = a.name)
order by hits DESC,id DESC

/*结果:
id name hits
----------- ---------- -----------
13 f 17
5 c 15
7 d 10
1 a 10
12 e 9
2 b 9
*/
------解决方案--------------------
create table tb
(
id int identity(1,1),
name varchar(2),
hits int
)
insert into tb
select 'a ',10 union all
select 'b ',9 union all
select 'a ',8 union all
select 'b ',6 union all
select 'c ',15 union all
select 'a ',5 union all
select 'd ',10 union all
select 'c ',12 union all
select 'd ',8 union all
select 'e ',7 union all