如何创建SQL查询WITH语句

Question

我创建了如下查询:

declare 
@FromDate DATETIME = '2021-09-01 12:44:56.493',
@ToDate DATETIME = '2021-09-01 12:44:56.493',
@StartRow INT=1,
@EndRow INT=5;
WITH SWIFTMessages AS (
Select M.DocumentId, 
M.FileName, 
M.DateAdded, 
M.NotificationSent, 
M.DateNotificationSent,
ROW_NUMBER() OVER (ORDER BY M.[DateAdded] ASC) AS RowNumber
from  [Swift].[MessageLog] AS M
Where ((@FromDate IS NULL OR @FromDate <= CAST(M.DateAdded AS DATE))
AND (@ToDate IS NULL OR @ToDate >= CAST(M.DateAdded AS DATE)))
), SWIFTMessageLogs AS (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY RowNumber DESC) AS RowNumberInverse
FROM SWIFTMessages
)
SELECT S.DocumentId, S.FileName, S.DateAdded, S.NotificationSent  -- getting error here
S.RowNumber + S.RowNumberInverse - 1 AS TotalRows
FROM SWIFTMessageLogs AS S
WHERE 
S.RowNumber >= @StartRow
AND S.RowNumber <= @EndRow
ORDER BY S.RowNumber
GO

但是我得到这样的错误

Msg 102, Level 15, State 1, Line 52'.'附近语法错误。

s下找不到任何东西不幸的是，我不能在这里指出这个问题。如果有人善意地指出我的问题，我会非常感激。提前感谢

Answer 1

maybe ?

SELECT
S.DocumentId,
S.FileName,
S.DateAdded,
S.NotificationSent, 
(S.RowNumber + S.RowNumberInverse - 1) AS TotalRows
FROM SWIFTMessageLogs AS S
WHERE S.RowNumber >= @StartRow
AND S.RowNumber <= @EndRow
ORDER BY S.RowNumber