我在数据库中有一个阅读列表。我把它作为个人训练的基础。随着时间的推移，它的功能不断增强，所以请原谅这种奇怪的结构。
我试图向这个查询添加分页，但我在尝试添加ROW_NUMBER方法时遇到了问题，因为我是如何实现"；Ord"；在我的询问中。
我能够利用级联的公共表表达式来完成这项工作，但我觉得必须有更好的方法。
以下是当显示作者的所有书籍时输出应该做的事情：
- 显示从最早完成到最新完成的书籍
- 然后显示当前定义的阅读列表；状态"；柱
- 然后显示该作者尚未列出的任何其他书籍
设置
CREATE TABLE #books(
ID int,
Author nvarchar(100),
Title nvarchar(100),
Series nvarchar(100),
BookNumber int,
DateCompleted smalldatetime,
[Status] int
)
INSERT INTO #books (ID, Author, Title, Series, BookNumber, DateCompleted, Status) VALUES
(1004,'J. K. Rowling','Harry Potter and the Chamber of Secrets','Harry Potter',2,'1-Mar-2020',1),
(1045,'J. K. Rowling','Harry Potter and the Prisoner of Azkaban','Harry Potter',3,NULL,2),
(1047,'J. K. Rowling','Harry Potter and the Goblet of Fire','Harry Potter',4,NULL,3),
(1048,'J. K. Rowling','Harry Potter and the Order of the Pheonix','Harry Potter',5,NULL,4),
(1049,'J. K. Rowling','Harry Potter and the Half Blood Prince','Harry Potter',6,NULL,NULL),
(1051,'J. K. Rowling','Harry Potter and the Deathly Hallows','Harry Potter',7,NULL,NULL),
(1185,'J. K. Rowling','Harry Potter and the Sorcerer''s Stone','Harry Potter',1,'1-Jan2020',NULL)
DECLARE @Author nVarChar(100) = 'J. K. Rowling'
DECLARE @Year nVarChar(5) = ''
DECLARE @PageNum INT = 1;
DECLARE @PageSize INT = 10;
原始查询
SELECT b.ID,
b.Title,
b.Series,
b.BookNumber,
ISNULL(b.Series, '') + ISNULL(' (book ' + LTRIM(STR(b.BookNumber)) + ')','') as SeriesDesc,
b.Author,
Case
When b.[Status] = 1 Then 'Completed'
When b.[Status] = 2 Then 'Reading'
When b.[Status] > 2 Then 'Next'
End as StatusDesc,
b.[Status],
b.DateCompleted,
CASE
WHEN b.DateCompleted is not NULL THEN 0
WHEN b.[Status] is not NULL THEN 5
WHEN b.[Status] is NULL THEN 9
END as Ord
FROM #books b
WHERE (((b.Author = @Author OR @Author = '') AND b.DateCompleted is not NULL) AND
(Year(b.DateCompleted) = @Year OR @Year = ''))
OR
(b.Author = @Author AND b.DateCompleted is NULL AND @Year = '')
ORDER BY Ord, [Status], DateCompleted, Series, BookNumber
这是我唯一能让它按预期工作的方法。
;WITH Step1 AS
(SELECT b.ID,
b.Title,
b.Series,
b.BookNumber,
ISNULL(b.Series, '') + ISNULL(' (book ' + LTRIM(STR(b.BookNumber)) + ')','') as SeriesDesc,
b.Author,
Case
When b.[Status] = 1 Then 'Completed'
When b.[Status] = 2 Then 'Reading'
When b.[Status] > 2 Then 'Next'
End as StatusDesc,
b.[Status],
b.DateCompleted,
CASE
WHEN b.DateCompleted is not NULL THEN 0
WHEN b.[Status] is not NULL THEN 5
WHEN b.[Status] is NULL THEN 9
END as Ord
FROM #books b
WHERE (((b.Author = @Author OR @Author = '') AND b.DateCompleted is not NULL) AND
(Year(b.DateCompleted) = @Year OR @Year = ''))
OR
(b.Author = @Author AND b.DateCompleted is NULL AND @Year = '')
),
Step2 AS
(SELECT ID, Title, Series, BookNumber, SeriesDesc, Author, StatusDesc, [Status], DateCompleted, Ord,
ROW_NUMBER() OVER (ORDER BY Ord, [Status], DateCompleted, Series, BookNumber) as RowNum
FROM Step1)
SELECT
ID, Title, Series, BookNumber, SeriesDesc, Author, StatusDesc, [Status], DateCompleted, Ord, RowNum
FROM Step2
WHERE @PageNum = 0 OR (RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND @PageNum * @PageSize)
ORDER BY RowNum
预期输出
|---------------------|-------------------------------------------|---------------------|------------------|-----------------------|------------------|---------------------|------------------|---------------------|------------------|
| ID | Title | Series | BookNumber | SeriesDesc | Author | StatusDesc | Status | DateCompleted | Ord |
|---------------------|-------------------------------------------|---------------------|------------------|-----------------------|------------------|---------------------|------------------|---------------------|------------------|
| 1185 | Harry Potter and the Sorcerer's Stone | Harry Potter | 1 | Harry Potter (book 1) | J. K. Rowling | NULL | NULL | 2020-01-01 00:00:00 | 0 |
| 1185 | Harry Potter and the Chamber of Secrets | Harry Potter | 2 | Harry Potter (book 2) | J. K. Rowling | Completed | 1 | 2020-03-01 00:00:00 | 0 |
| 1185 | Harry Potter and the Prisoner of Azkaban | Harry Potter | 3 | Harry Potter (book 3) | J. K. Rowling | Reading | 2 | NULL | 5 |
| 1185 | Harry Potter and the Goblet of Fire | Harry Potter | 4 | Harry Potter (book 4) | J. K. Rowling | Next | 3 | NULL | 5 |
| 1185 | Harry Potter and the Order of the Pheonix | Harry Potter | 5 | Harry Potter (book 5) | J. K. Rowling | Next | 4 | NULL | 5 |
| 1185 | Harry Potter and the Half Blood Prince | Harry Potter | 6 | Harry Potter (book 6) | J. K. Rowling | NULL | NULL | NULL | 9 |
| 1185 | Harry Potter and the Deathly Hallows | Harry Potter | 7 | Harry Potter (book 7) | J. K. Rowling | NULL | NULL | NULL | 9 |
|---------------------|-------------------------------------------|---------------------|------------------|-----------------------|------------------|---------------------|------------------|---------------------|------------------|
那么偏移量提取如何：
SELECT b.ID,
b.Title,
b.Series,
b.BookNumber,
ISNULL(b.Series, '') + ISNULL(' (book ' + LTRIM(STR(b.BookNumber)) + ')','') as SeriesDesc,
b.Author,
Case
When b.[Status] = 1 Then 'Completed'
When b.[Status] = 2 Then 'Reading'
When b.[Status] > 2 Then 'Next'
End as StatusDesc,
b.[Status],
b.DateCompleted,
CASE
WHEN b.DateCompleted is not NULL THEN 0
WHEN b.[Status] is not NULL THEN 5
WHEN b.[Status] is NULL THEN 9
END as Ord
FROM #books b
WHERE (((b.Author = @Author OR @Author = '') AND b.DateCompleted is not NULL) AND
(Year(b.DateCompleted) = @Year OR @Year = ''))
OR
(b.Author = @Author AND b.DateCompleted is NULL AND @Year = '')
ORDER BY Ord, [Status], DateCompleted, Series, BookNumber
OFFSET @PageSize*@PageNum ROWS
FETCH NEXT @PageSize ROWS ONLY
@PageNum应从0 开始