Question

sql 取中间几条记录

Answer 1

--从Table 表中取出第 m 条到第 n 条的记录：(Not In 版本)
SELECT TOP n-m+1 * FROM Table WHERE (id NOT IN (SELECT TOP m-1 id FROM Table ))
--从TABLE表中取出第m到n条记录 (Exists版本)
SELECT TOP n-m+1 * FROM TABLE AS a WHERE Not Exists
(Select * From (Select Top m-1 * From TABLE order by id) b Where b.id=a.id )
Order by id
--m为上标，n为下标,例如取出第8到12条记录,m=8,n=12,Table为表名
Select Top n-m+1 * From Table
Where Id(Select Max(Id) From
(Select Top m-1 Id From Table Order By Id Asc) Temp)
-----------------------------------------------------------------------------------------------------------------------------------------
表pictures中有两个字段：id与title。id是自动编号的­
表中有5条记录：1--p1,2--p2,3--p3,4--p4,5--p5­
一、找到了一个小规律
string sqlstr = "select top 4 * from pictures order by id desc "; //查询结果p5,p4,p3,p2---说明是整个表先进行排序，再进行查询的­
string sqlstr = "select top 3 * from (select top 4 * from pictures order by id desc) "; //-------p5,p4,p3­
string sqlstr = "select top 3 * from (select top 4 * from pictures order by id desc) order by id desc";//-------p5,p4,p3
string sqlstr = "select top 3 * from (select top 4 * from pictures order by id desc) order by id asc"; //-------p2,p3,p4
二、获取单条记录：­
假设表中一共有counts条记录，现在想要查询第n条记录，则sql语句应是：­
select top 1 * from (select top (counts-n+1) * from pictures order by id desc) order by id asc­
第三条记录：­
string sqlstr = "select top 1 * from (select top 3 * from pictures order by id desc) order by id asc";//-------p3­
三、获取表中多条连续的记录­
假设表中一共有counts条记录，现在想要查询第n到第m条的记录，则sql语句应是：­
select top (m-n+1) * from (select top (counts-n+1) * from pictures order by id desc) order by id asc­
获取第二到第四条记录：­

Answer 2

1.
oracle数据库
select
*
from
tablename
where
rownum
<=
n
2.
infomix数据库
select
first
n
*
from
tablename
3.
db2数据库
select
*
from
(select
*
row_number()
over({order
by
col1
desc})
as
rownum
from
tablename)
where
rownum
<=
n
或者
select
column
from
tablename
fetch
first
n
rows
only
4.
sql
server数据库
select
top
n
*
from
tablename
5.
sybase数据库
set
rowcount
n
go
select
*
from
tablename
6.
mysql数据库
select
*
from
tablename
limit
n
7.
foxpro数据库
select
*
top
n
from
tablename
order
by
column
以下示例从表
[tablename]
中读取符合查询条件的前10条记录的sql语句
1.access
select
top
(10)
*
from
[tablename]
where
[query
condition]
1.1
带order
by的查询限制
access中对select
top的语句支持有限，如果要在查询top语句的后面使用order
by，则order
by排序字段必须是无重复值，如果有重复值的话，那么这个top很可能会失效，会返回所有记录。
解决办法：在order
by
最后面加入主键id,如：
select
top
10
from
[tablename]
order
by
排序字段1,id
1.2
带子查询的示例
假如id是表[tablename]的主键，以下语句期望返回三条记录，但结果返回4条记录
select
top
3
*
from
[tablename]
where
id
in(是个子查询，结果比如为1,2,3,4)
解决办法
select
top
3
*
from
[tablename]
where
id
in(是个子查询，结果比如为1,2,3,4)
order
by
id
2
db2
select
column
from
[tablename]
where
[query
condition]
fetch
first
10
rows
only
3
mysql
select
*
from
[tablename]
where
[query
condition]
limit
10
4
sql
server
4.1
读取前10条
select
top
(10)
*
from
[tablename]
where
[query
condition]
4.2
读取后10条
select
top
(10)
*
from
[tablename]
order
by
id
desc
4.3
按照某个排序，第5到10这几个记录
select
top
6
*
from
[tablename]
where
id
not
in(select
top
4
id
from
[tablename])
5
oracle
select
*
from
[tablename]
where
rownum<=10

Answer 3

SELECT * FROM (SELECT row_number() OVER (order by (select 1)) AS no, * FROM Table_name) t WHERE no >= n AND no <=n;
把Table_name换成你的表名，no>=n 中的n换成你要查询第几条到第几条的记录；