数据如下
表
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倒序
分不在高,肯定有技术研究
表
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倒序
分不在高,肯定有技术研究
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
where not exists (select 1 from tablename
where name=a.name and (hits>a.hits
or hits=a.hits and id>a.id)
order by a.hits desc,id desc
group by name,id
order by max_its desc
第二种方案行不通。
我要查询10W条记录,所以要求查询效率高点的SQL语句 。
我本来写的是
select * from hits a where not exists(select null from hits b where a.name = b.name and a.hits<b.hits) order by hits desc
但是没有过滤到重复的记录 ,所以请大家帮助了。
呵呵
是缺少了一个)。呵呵。
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',15select * 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
7 d 10
1 a 10
12 e 9
2 b 9
*/drop table tt