gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 05:13:27 32 4
gpt4 key购买 nike

这是我不正确的查询:

;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 的上下文中。我如何实现这一目标?

最佳答案

您可以创建另外两个 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

关于sql - 如何在 CTE 中编写 2 个 select 语句?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15017721/

32 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com