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

[zyciis229]

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

谢谢

[最后一只恐龙]

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

[最后一只恐龙]

求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都忘了

[RexZheng]

选择从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

[ly_0205]

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

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

[amandag]

求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
================================================

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

[hertcloud]

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

[hertcloud]

对于 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

[zyciis229]

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条数据进行比较会不会太慢了

[hertcloud]

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

[hertcloud]

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

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



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

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

[shadow841112]

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

[hertcloud]

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

[anan221]

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