sql - 如何在 CTE 中编写 2 个 select 语句？

Question

这是我不正确的查询:

;With normal As
    (   
        Select  ROW_NUMBER() Over (Order by RecordingDateTime) as RowNum, 
                DocumentID, 
                CreatedByAccountID, 
                JurisdictionID,
                InstrumentID
            --  Cast(InstrumentID as decimal(18)) as InstrumentID
            From Documents  Where RecordingDateTime IS NOT NULL
    )
    Select Top 1 @PreviousInstrumentID = InstrumentID,
                @PreviousDocumentID = DocumentID,
                @PreviousCreatedByAccountID = CreatedByAccountID,
                @PreviousBelongsToJurisdictionID = JurisdictionID
            From normal
                Where RowNum = @RowNum - 1

    Select Top 1 @NextInstrumentID = InstrumentID,
                @NextDocumentID = DocumentID,
                @NextCreatedByAccountID = CreatedByAccountID,
                @NextBelongsToJurisdictionID = JurisdictionID
            From normal
                Where RowNum = @RowNum + 1

我希望两个 select 语句都在 CTE 的上下文中。我如何实现这一目标？

Answer 1

您可以创建另外两个 CTE，并在您的选择语句中使用它们:

;With normal As
    (   
        Select  ROW_NUMBER() Over (Order by RecordingDateTime) as RowNum, 
                DocumentID, 
                CreatedByAccountID, 
                JurisdictionID,
                InstrumentID
            --  Cast(InstrumentID as decimal(18)) as InstrumentID
            From Documents  Where RecordingDateTime IS NOT NULL
    )
, PrevRow AS (
SELECT InstrumentID, DocumentID, CreatedByAccountID, JurisdictionID
FROM normal
WHERE RowNum = @RowNum - 1
), NextRow AS (
SELECT InstrumentID, DocumentID, CreatedByAccountID, JurisdictionID
FROM normal
WHERE RowNum = @RowNum + 1
)
Select Top 1    @PreviousInstrumentID = PrevRow.InstrumentID,
                @PreviousDocumentID = PrevRow.DocumentID,
                @PreviousCreatedByAccountID = PrevRow.CreatedByAccountID,
                @PreviousBelongsToJurisdictionID = PrevRow.JurisdictionID,
                @NextInstrumentID = NextRow.InstrumentID,
                @NextDocumentID = NextRow.DocumentID,
                @NextCreatedByAccountID = NextRow.CreatedByAccountID,
                @NextBelongsToJurisdictionID = NextRow.JurisdictionID

FROM PrevRow 
FULL OUTER JOIN NextRow ON 1=1