求SQL 取按浏览量排序第2-10位的语句
本帖最后由 zodrao 于 2013-09-18 20:41:11 编辑 id name hits
1 a 110
2 b 60
3 c 200
4 d 15
5 e 80
....
希望显示的是
1.a 110
2.e 80
3.b 60
4.d 15
....
谢谢大侠了
[解决办法]
select *
from
(select *,rn=ROW_NUMBER() OVER(ORDER BY hits) from tb)
where rn between 2 and 10
create table zd
(id int, name varchar(5), hits int)
insert into zd
select 1, 'a', 110 union all
select 2, 'b', 60 union all
select 3, 'c', 200 union all
select 4, 'd', 15 union all
select 5, 'e', 80
select identity(int,1,1) 'rn', id,name,hits
into #t
from zd
order by hits desc
select rn-1 'rn',name,hits
from #t
where rn between 2 and 10
/*
rn name hits
----------- ----- -----------
1 a 110
2 e 80
3 b 60
4 d 15
(4 row(s) affected)
*/
create table #tb(id int,name varchar(10),hits int)
insert into #tb
select 1,'a',110 union all
select 2,'b',60 union all
select 3,'c',200 union all
select 4,'d',15 union all
select 5,'e',80
select id,name,hits from (
select ROW_NUMBER()over(order by hits desc) as num,
* from #tb)a
where num between 2 and 10
************************************************************************************************
id name hits
----------- ---------- -----------
1 a 110
5 e 80
2 b 60
4 d 15
(4 行受影响)
