sql - 子查询在将其与不同表中的下一行进行比较时应返回 Null

Question

我的 T-SQL 子查询返回超过 1 行：

WITH ReportView AS
(
    SELECT 
        PMA.AssetName, ReleaseDt, ExpiresDt, TicketNumber, ChangeDt, 
        ChangeReasonCd, 
        ROW_NUMBER() OVER (PARTITION BY PMA.AssetName, ReleaseDt, ExpiresDt, TicketNumber, ChangeReasonCd 
                           ORDER BY ChangeDt ASC) AS ROW_NUM 
    FROM 
        pmm.pmmreleaserequest PRR WITH (nolock)
    LEFT JOIN 
        pmm.PmmManagedAccount AS PMA WITH (nolock) ON PRR.ManagedAccountID = PMA.ManagedAccountID
    LEFT JOIN 
        dbo.ManagedEntity AS ME WITH (nolock) ON PRR.ManagedSystemID = ME.ManagedEntityID
    LEFT JOIN 
        dbo.Asset AS AST WITH (nolock) ON ME.AssetID = AST.AssetID
    LEFT JOIN 
        pmm.PmmLogChange AS PLC WITH (nolock) ON PRR.ManagedAccountID = PLC.ManagedAccountID 
                                              AND PRR.ExpiresDt < PLC.ChangeDt
)
SELECT * 
FROM ReportView 
WHERE ROW_NUM = 1

如何比较 ChangeDt 的当前行与 ReleaseDt 的下一行。Example ChangeDt(current row) < ReleaseDt(Next row) 我怎样才能把这个条件。

标签： sqlsql-serverlead

Answer 1

这是如何row_num定义的：

    ROW_NUMBER() OVER (PARTITION BY PMA.AssetName, ReleaseDt, ExpiresDt, TicketNumber, ChangeReasonCd 
                       ORDER BY ChangeDt ASC) AS ROW_NUM 

它将为每个列中的唯一组合返回一行PARTITION BY。PMA.AssetName即每/ ReleaseDt/ ExpiresDt/ TicketNumber/一行ChangeReasonCd。

我希望会有不止一种这样的组合。

如果您只想要一行，请使用SELECT TOP (1)or OFFSET/FETCH。