数据如下
表
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倒序
分不在高,肯定有技术研究
表
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倒序
分不在高,肯定有技术研究
把这个代码 放到 FROM 后边就可以了
用一句SQL语句来实现
书上的书句就是很单一的一个查寻。
这里涉及到我想不只是一个简单查询吧。
呵呵。如果按您的方法的话就根本取到我上面的数据。
请把数据看仔细,谢谢
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
楼上的是一种方法,还有一种方法就是使用MAX函数来处理!
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
*/
(
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
我也贴一下自己的
select name,max(hits) as hits into #t from hits where name in (select name from hits group by name) group by name order by hits desc
select h.id,h.name,h.hits from hits as h,#t t where h.name=t.name and h.hits=t.hits order by t.hits desc
--select * from #t
drop table #t