根据问题的回答者所说:
请查看此链接。它有一个不同的解决方案,对于提出问题的人来说似乎有效。我正在尝试找到这样的解决方案。
在SQL Server 2005中使用ROW_NUMBER() OVER ()进行排序的分页查询
~Joseph
"方法1"类似于链接问题中的OP的查询,而"方法2"类似于所选答案中的查询。您需要查看此答案中链接的代码才能真正了解正在发生什么,因为所选答案中的代码已被修改以使其工作。请尝试以下操作:
DECLARE @YourTable table (RowID int not null primary key identity, Value1 int, Value2 int, value3 int)
SET NOCOUNT ON
INSERT INTO @YourTable VALUES (1,1,1)
INSERT INTO @YourTable VALUES (1,1,2)
INSERT INTO @YourTable VALUES (1,1,3)
INSERT INTO @YourTable VALUES (1,2,1)
INSERT INTO @YourTable VALUES (1,2,2)
INSERT INTO @YourTable VALUES (1,2,3)
INSERT INTO @YourTable VALUES (1,3,1)
INSERT INTO @YourTable VALUES (1,3,2)
INSERT INTO @YourTable VALUES (1,3,3)
INSERT INTO @YourTable VALUES (2,1,1)
INSERT INTO @YourTable VALUES (2,1,2)
INSERT INTO @YourTable VALUES (2,1,3)
INSERT INTO @YourTable VALUES (2,2,1)
INSERT INTO @YourTable VALUES (2,2,2)
INSERT INTO @YourTable VALUES (2,2,3)
INSERT INTO @YourTable VALUES (2,3,1)
INSERT INTO @YourTable VALUES (2,3,2)
INSERT INTO @YourTable VALUES (2,3,3)
INSERT INTO @YourTable VALUES (3,1,1)
INSERT INTO @YourTable VALUES (3,1,2)
INSERT INTO @YourTable VALUES (3,1,3)
INSERT INTO @YourTable VALUES (3,2,1)
INSERT INTO @YourTable VALUES (3,2,2)
INSERT INTO @YourTable VALUES (3,2,3)
INSERT INTO @YourTable VALUES (3,3,1)
INSERT INTO @YourTable VALUES (3,3,2)
INSERT INTO @YourTable VALUES (3,3,3)
SET NOCOUNT OFF
DECLARE @PageNumber int
DECLARE @PageSize int
DECLARE @SortBy int
SET @PageNumber=3
SET @PageSize=5
SET @SortBy=1
;WITH PaginatedYourTable AS (
SELECT
RowID,Value1,Value2,Value3
,CASE @SortBy
WHEN 1 THEN ROW_NUMBER() OVER (ORDER BY Value1 ASC)
WHEN 2 THEN ROW_NUMBER() OVER (ORDER BY Value2 ASC)
WHEN 3 THEN ROW_NUMBER() OVER (ORDER BY Value3 ASC)
WHEN -1 THEN ROW_NUMBER() OVER (ORDER BY Value1 DESC)
WHEN -2 THEN ROW_NUMBER() OVER (ORDER BY Value2 DESC)
WHEN -3 THEN ROW_NUMBER() OVER (ORDER BY Value3 DESC)
END AS RowNumber
FROM @YourTable
)
SELECT
RowID,Value1,Value2,Value3,RowNumber
,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
FROM PaginatedYourTable
WHERE RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
ORDER BY RowNumber
;WITH PaginatedYourTable AS (
SELECT
RowID,Value1,Value2,Value3
,ROW_NUMBER() OVER
(
ORDER BY
CASE @SortBy
WHEN 1 THEN Value1
WHEN 2 THEN Value2
WHEN 3 THEN Value3
END ASC
,CASE @SortBy
WHEN -1 THEN Value1
WHEN -2 THEN Value2
WHEN -3 THEN Value3
END DESC
) RowNumber
FROM @YourTable
)
SELECT
RowID,Value1,Value2,Value3,RowNumber
,@PageNumber AS PageNumber, @PageSize AS PageSize, @SortBy AS SortBy
FROM PaginatedYourTable
WHERE
RowNumber>=(@PageNumber-1)*@PageSize AND RowNumber<=(@PageNumber*@PageSize)-1
ORDER BY
CASE @SortBy
WHEN 1 THEN Value1
WHEN 2 THEN Value2
WHEN 3 THEN Value3
END ASC
,CASE @SortBy
WHEN -1 THEN Value1
WHEN -2 THEN Value2
WHEN -3 THEN Value3
END DESC
输出:
RowID Value1 Value2 Value3 RowNumber PageNumber PageSize SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10 2 1 1 10 3 5 1
11 2 1 2 11 3 5 1
12 2 1 3 12 3 5 1
13 2 2 1 13 3 5 1
14 2 2 2 14 3 5 1
(5 row(s) affected
RowID Value1 Value2 Value3 RowNumber PageNumber PageSize SortBy
------ ------ ------ ------ ---------- ----------- ----------- -----------
10 2 1 1 10 3 5 1
11 2 1 2 11 3 5 1
12 2 1 3 12 3 5 1
13 2 2 1 13 3 5 1
14 2 2 2 14 3 5 1
(5 row(s) affected)
ROW_NUMBER() OVER (ORDER BY employee_id) > 0
这个条件将始终被判定为TRUE
。 - Quassnoi