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
select 'f ',5 union all
select 'e ',9 union all
select 'f ',17
go
select * from
(select a.* from tb a inner join tb b on a.id <> b.id and a.name=b.name and (select count(*) from tb where name=a.name and hits> a.hits)=0) c
group by c.id,c.name,c.hits order by c.hits desc
id name hits
----------- ---- -----------
13 f 17
5 c 15
1 a 10
7 d 10
2 b 9
12 e 9
