一个表中有100条数据，我想每次查10条直到查完，请问有什么好的方法么

[xzxmustwin]

一个表中有100条数据，我想每次查10条直到查完，请问有什么好的方法么
(不是分页)

[xzxmustwin]

引用 1 楼 eaqpi 的回复:

WITH sss AS( SELECT *,ROW_NUMBER() OVER(ORDER BY 字段) AS rowNum FROM [table] ) SELECT * FROM sss WHERE rowNum BETWEEN 31 AND 40

额 不是分页啦 不过还是谢谢你

[牛尚小又何妨]

首先给表排序，记录当次查询的最大值（0开始）在临时表或者实体表，每次查询10条，跟新最大是+10

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(id INT IDENTITY(1,1) PRIMARY KEY,n NVARCHAR(10))
GO
INSERT INTO t(n)
SELECT left(NEWID(),10) 
FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 100;

DECLARE @t TABLE (rowNum INT IDENTITY(1,1) PRIMARY KEY,id INT)

INSERT INTO @t(id)
SELECT id
FROM t

DECLARE @i INT,@imax INT
SELECT @i=1,@imax=MAX(rowNum) FROM @t

WHILE @i<=@imax
BEGIN
	SELECT t.id,t.n 
	FROM t INNER JOIN @t AS b ON t.id=b.id AND b.rowNum BETWEEN @i AND @i+9 
	
	SET @i=@i+10
END

[eaqpi]

WITH sss AS( SELECT *,ROW_NUMBER() OVER(ORDER BY 字段) AS rowNum FROM [table] ) SELECT * FROM sss WHERE rowNum BETWEEN 31 AND 40

[xzxmustwin]

引用 2 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(id INT IDENTITY(1,1) PRIMARY KEY,n NVARCHAR(10))
GO
INSERT INTO t(n)
SELECT left(NEWID(),10) 
FROM [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number BETWEEN 1 AND 100;

DECLARE @t TABLE (rowNum INT IDENTITY(1,1) PRIMARY KEY,id INT)

INSERT INTO @t(id)
SELECT id
FROM t

DECLARE @i INT,@imax INT
SELECT @i=1,@imax=MAX(rowNum) FROM @t

WHILE @i<=@imax
BEGIN
	SELECT t.id,t.n 
	FROM t INNER JOIN @t AS b ON t.id=b.id AND b.rowNum BETWEEN @i AND @i+9 
	
	SET @i=@i+10
END

大概思路了解了 结贴！

[xiaoxiangqing]

先编号，通过编号来查