sql选择指定条目的记录第M到N条

来源：互联网发布：php在线文档系统编辑：程序博客网时间：2024/06/05 20:30

1、这是第M到N条的语句

select top (n-m+1) * from users where username not in (select top (m-1) username from users order by username) order by username

例：
SELECT TOP 5 UID,BuildingType,UsingType FROM Table_Housing WHERE
(DealType=1 OR DealType=3)
AND
HousingID NOT IN (SELECT TOP 5 HousingID FROM Table_Housing WHERE (DealType=1 OR DealType=3) ORDER BY HousingID)
ORDER BY HousingID

再加一个表

SELECT TOP 5 UID,BuildingType,UsingType FROM Table_Housing
INNER JOIN Table_Users ON (Table_Housing.UID=Table_Users.UID)
WHERE
(DealType=1 OR DealType=3)
AND
HousingID NOT IN (SELECT TOP 5 HousingID FROM Table_Housing WHERE (DealType=1 OR DealType=3) ORDER BY HousingID)
ORDER BY HousingID

2、利用临时表+IDENTITY（函数）来实现，举例如下：

SELECT IDENTITY(smallint, 1, 1) AS ROWNUM, OTHER FIELDS。。。。 INTO 临时表 FROM 来源

这样，在临时表里就有一个字段ROWNUM，其值就类似ORC里面的ROWNUM，用这个方法的话不必对来源进行排序，而且这个ROWNUM的值也不一定要从1开始，也可以从其他的值开始，如果我想要从11开始，那么上述语句我只需要把IDENTITY(smallint, 1, 1) AS ROWNUM改成IDENTITY(smallint, 11, 1) AS ROWNUM就可以了。

3、利用排序结合统计子查询来实现，但是此方法需要按一定的字段排序，举例如下表A

field1 field2 field3

12 22 23

14 33 23

16 33 22

17 43 23

假设我现在需要过滤field3=23的记录并加上ROWNUM，那么语句如下

SELECT (SELECT COUNT(*) FROM A WHERE field1=TMPA.field1) AS ROWNUM, TMPA.* FROM A AS TMPA WHERE field3=23 ORDER BY field1

返回的结果为

ROWNUM field1 field2 field3

1 12 22 23

2 14 33 23

3 17 43 23