|M| 求SQL查询语句，查询出表中第11条到20条的记录

如select * from tab1
现在要求是
select 11 to 20 from tab 这样的

谢谢

13 个解决方案

#1

按id排序
Select top 10 From tab1 where id not in
(Select top 10 from tab1 order by id)
order by id

#2

求m~n条
Select top (n-m+1) * From tab1 where id not in
(Select top m id from tab1 order by id)
order by id

上个帖子top 10后面把*和id都忘了

#3

选择从10到15的记录

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

or

selecct top 5 * from table where id not in (select top 10 id from table order by id asc) order by id asc

#4

sql server 这样: 其它数据库可能有支持你写的那种方式

select top 20 identity(int,1,1) lineSN,* into # from tab1
select * from # where lineSN > 10
drop table #

#5

求m~n条
Select top (n-m+1) * From tab1 where id not in
(Select top m id from tab1 order by id)
order by id
================================================

这个是个比较经典的案例了

#6

select top 10 [LogID], [UserID], [TrueName] from TB_ActiveLog where LogID not in(select top 10 LogID from TB_ActiveLog order by LogID DESC ) order by LogID DESC

#7

对于 GUID只能使用上面的形式
对于int类型可以使用效率更好的.

select top 10 [LogID], [UserID] from TB_ActiveLog where LogID <
(select min(LogID) from (select top 10 LogID from TB_ActiveLog order by LogID DESC ) AS TBMinID) order by LogID DESC

#8

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

这里面有对id 进行排序
要是我本来要查询的数据id是很乱的而不是顺的就不行了

selecct top 5 * from table where id not in (select top 10 id from table order by id asc) order by id asc

这条看起来还有点可行但是要是
selecct top 5 * from table where id not in (select top 10000 id from table order by id asc) order by id asc
这样要对10000条数据进行比较会不会太慢了

#9

这里有这两种形式的分页通用过程
http://blog.csdn.net/hertcloud/category/281167.aspx

#10

not in在几十万数据的情况还可以...

top max 可以应付千万级的分页.

但是楼主要注意点这两种分页模式下你的排序主键都必须的不重复值的.

如果重复那么sql 2005以前需要临时表解决
sql 2005 有 row_number 很好用

#11

select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

#12

select  top 10 [LogID], [UserID] from  TB_ActiveLog where LogID <
(select min(LogID) from (select  top 10 LogID from TB_ActiveLog order by LogID  DESC ) AS TBMinID) order by LogID  DESC

这个是数据倒排的时候  从第一条开始

select  top 10 [LogID], [UserID] from  TB_ActiveLog where LogID >
(select max(LogID) from (select  top 10 LogID from TB_ActiveLog order by LogID  ASC ) AS TBMinID) order by LogID  ASC

#13

Select top 10 From tab1 where id not in
(Select top 10 from tab1 order by id)

#1