b******n
发帖数: 1629 | 1 select top n * from table order by column
是返回前n行，有没有办法返回荣国column排序的第n1到n2行这个范围？ |
B*****g
发帖数: 34098 | 2 呵呵，partition by
【在 b******n 的大作中提到】
: select top n * from table order by column
: 是返回前n行，有没有办法返回荣国column排序的第n1到n2行这个范围？
|
b******n
发帖数: 1629 | 3 我要做的只是一个数字，比如第一次取1～1000000，第二次取1000001～2000000，这个
partition by能搞定么，ms不行？
【在 B*****g 的大作中提到】
: 呵呵，partition by
|
p********l
发帖数: 279 | 4 You can try to use CTE and row_number:
WITH TableRN AS
(
SELECT ROW_NUMBER() OVER(ORDER BY col1) AS rownum,
col1,
col2
FROM Table1
)
SELECT rownum, col1, col2
FROM TableRN
WHERE rownum BETWEEN n1 AND n2
ORDER BY rownum;
【在 b******n 的大作中提到】
: select top n * from table order by column
: 是返回前n行，有没有办法返回荣国column排序的第n1到n2行这个范围？
|
y****9
发帖数: 144 | 5 This is a well-know SQL problem: pagenation.
http://sqlserverplanet.com/sql-server/best-sql-server-paginatio
There are two methods described there. the second one is CTE. the poster
thinks the first one is better. |
B*****g
发帖数: 34098 | 6 google "partition by", hehe
http://msdn.microsoft.com/en-us/library/ms189461.aspx
【在 b******n 的大作中提到】
: 我要做的只是一个数字，比如第一次取1～1000000，第二次取1000001～2000000，这个
: partition by能搞定么，ms不行？
|
i*****w
发帖数: 75 | 7 Simple SQL scripts should be able to handle this situation:
Set NOCOUNT ON
CREATE TABLE #tbl(id INT, LineItem Varchar(100))
DECLARE @i INT
SET @i =1
WHILE @i <= 2000
BEGIN
INSERT INTO #tbl
SELECT @i, 'This is line ' + CAST(@i as varchar(20))
SET @i = @i + 1
END
Declare @n1 varchar(20), @n2 varchar(20)
SET @n1 = '50'
SET @n2 = '700'
Declare @SQLToExecute varchar(4000)
SET @SQLToExecute = 'SELECT * FROM ( SELECT TOP ' + @n2 + ' * FROM #tbl
ORDER BY ID) a EXCEPT SELECT * FROM ( SELECT TOP ' + @n1 + ' * FROM #tbl
ORDER BY ID ) b '
Exec(@SQLToExecute)
DROP TABLE #Tbl
【在 b******n 的大作中提到】
: select top n * from table order by column
: 是返回前n行，有没有办法返回荣国column排序的第n1到n2行这个范围？
|
b******n
发帖数: 1629 | |
g***l
发帖数: 18555 | 9 搞个RANK不就解决了。查一下RANK FUNCTION就知道了 |
