怎样取出表中的第N条数据,头疼,在线等

[oop80]

create table tmp
(
a varchar(100)
b varchar(200)
)
go


insert into tmp
select 'row1', 'value111'
union select 'row2','value222'
union select 'row3','value333'
go


请教怎样取出表中的第N条数据,
不要告诉我用 select * from tmp where a='rowX'

[paoluo]

eg:

--第二條
select TOP 1 * From (Select TOP 2 * From tmp Order By a) A Order By a Desc

[paoluo]

或者

select TOP 1 * From tmp Where a Not In (Select TOP 2 a From tmp Order By a) Order By a

[OracleRoob]

--第一种方法
--第11条到第20条，共选出10条记录
select *
from (select top 10 * from (select top 20 * from 表名 order by ID) t1 order by ID desc) t2
order by ID


--第二种方法
--第11条到第20条，共选出10条记录
select top 10 *
from 表名
where ID>(select max(ID) from (select top 10 ID from 表名 order by ID) t1)
order by ID

[oop80]

哦,有启发了,来试验一下.

[SoftwKLC]

---如果你表有个自增ID的话那主好办
Select
Top 1 *
From (
Select Top 2 * From tmp) T
Order By id Desc

[撸大湿]

create table tmp
(
a varchar(100),
b varchar(200)
)
go


insert into tmp
select 'row1', 'value111'
union select 'row2','value222'
union select 'row3','value333'
go





select * from tmp a where (select count(1) from tmp where a<=a.a)=2 --数量

[SoftwKLC]

/*取N到M条记录公式并按ID升序排列*/
Select
*
From (
Select
Top M-N+1 *
From (
Select Top M * From 表
) T
Order By ID Desc
) TT
Order By ID

/*取N到M条记录公式并按ID降序排列*/
Select
Top M-N+1 *
From (
Select Top M * From 表
) T
Order By ID Desc

[SoftwKLC]

---测试
Select Top 1000 ID=Identity(int,1,1) Into # From sysColumns A,sysColumns B

---测试1，取第11到30条数据按ID升序排
Select
*
From (
Select
Top 20 * /*30-11+1=20*/
From (
Select Top 30 * From #
) T
Order By ID Desc
) TT
Order By ID

---测试2，取第89到98条数据按ID升序排
Select
*
From (
Select
Top 10 * /*98-89+1=10*/
From (
Select Top 98 * From #
) T
Order By ID Desc
) TT
Order By ID

[oop80]

搞定!

[brother2605]

有排序规则的
select top 1 a.* from (select top N from table_name order by 排序段 asc) a
order by 排序字段 desc
没有排序规则的
select id=Identity(int,1,1),a.* into #tmp from table_name a
select*from #tmp where id=N