使用RowNumber实现SQL Server同表自连接同行展示无结果排查

最近我遇到个问题：想把同一张表的最新两条记录的值展示在同一行，计划用多次自连接，分别命名为LatestNote和SecondLatestNote，用ROW_NUMBER()来区分记录的新旧顺序，但执行下面的SQL后却没有返回任何结果，麻烦帮忙看看问题出在哪？

DECLARE @DateFrom AS DATETIME = CONVERT(DateTime, '2017-01-01 00:00:00.000', 120)
DECLARE @DateTo AS DATETIME = CONVERT(DateTime, '2017-12-31 23:59:59.999', 120) -- 补全你SQL里的笔误

SELECT 
    LatestNote.ID,
    LatestNote.Content AS LatestContent,
    SecondLatestNote.Content AS SecondLatestContent
FROM
    (SELECT *, ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS RowNum 
     FROM Notes 
     WHERE CreateTime BETWEEN @DateFrom AND @DateTo) AS LatestNote
JOIN
    (SELECT *, ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS RowNum 
     FROM Notes 
     WHERE CreateTime BETWEEN @DateFrom AND @DateTo) AS SecondLatestNote
ON LatestNote.ID = SecondLatestNote.ID
WHERE
    LatestNote.RowNum = 1
    AND SecondLatestNote.RowNum = 2

问题根源分析 #

你这里的自连接条件完全写错了！ON LatestNote.ID = SecondLatestNote.ID意味着你在找同一条记录既要满足是第1条（最新）又要满足是第2条（次新），这显然是矛盾的，根本不可能存在这样的记录，所以自然返回空结果。

正确的解决思路 #

我们需要的是同一张表中两条不同的记录（最新和次新），所以自连接的条件不应该是ID相等，而是要根据实际需求调整：

场景1：全表取最新两条记录

如果是针对全表的最新两条记录，不需要按维度分组，正确的写法可以用交叉连接（或者无关联的连接），再通过RowNum筛选：

DECLARE @DateFrom AS DATETIME = CONVERT(DateTime, '2017-01-01 00:00:00.000', 120)
DECLARE @DateTo AS DATETIME = CONVERT(DateTime, '2017-12-31 23:59:59.999', 120)

SELECT 
    LatestNote.Content AS LatestContent,
    SecondLatestNote.Content AS SecondLatestContent
FROM
    (SELECT *, ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS RowNum 
     FROM Notes 
     WHERE CreateTime BETWEEN @DateFrom AND @DateTo) AS LatestNote
CROSS JOIN
    (SELECT *, ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS RowNum 
     FROM Notes 
     WHERE CreateTime BETWEEN @DateFrom AND @DateTo) AS SecondLatestNote
WHERE
    LatestNote.RowNum = 1
    AND SecondLatestNote.RowNum = 2

场景2：按维度分组取每个分组的最新两条

如果你的需求是按某个维度（比如UserId）分组，每个分组下取最新和次新记录并排，那应该在ROW_NUMBER()里加上PARTITION BY，然后自连接时按分组字段关联：

DECLARE @DateFrom AS DATETIME = CONVERT(DateTime, '2017-01-01 00:00:00.000', 120)
DECLARE @DateTo AS DATETIME = CONVERT(DateTime, '2017-12-31 23:59:59.999', 120)

SELECT 
    LatestNote.UserId,
    LatestNote.Content AS LatestContent,
    SecondLatestNote.Content AS SecondLatestContent
FROM
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY CreateTime DESC) AS RowNum 
     FROM Notes 
     WHERE CreateTime BETWEEN @DateFrom AND @DateTo) AS LatestNote
JOIN
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY CreateTime DESC) AS RowNum 
     FROM Notes 
     WHERE CreateTime BETWEEN @DateFrom AND @DateTo) AS SecondLatestNote
ON LatestNote.UserId = SecondLatestNote.UserId
WHERE
    LatestNote.RowNum = 1
    AND SecondLatestNote.RowNum = 2

额外优化建议 #

如果只是取全表的最新两条记录，其实可以用更简洁的条件聚合写法，避免自连接的冗余：

DECLARE @DateFrom AS DATETIME = CONVERT(DateTime, '2017-01-01 00:00:00.000', 120)
DECLARE @DateTo AS DATETIME = CONVERT(DateTime, '2017-12-31 23:59:59.999', 120)

SELECT
    MAX(CASE WHEN RowNum = 1 THEN Content END) AS LatestContent,
    MAX(CASE WHEN RowNum = 2 THEN Content END) AS SecondLatestContent
FROM(
    SELECT Content, ROW_NUMBER() OVER (ORDER BY CreateTime DESC) AS RowNum
    FROM Notes
    WHERE CreateTime BETWEEN @DateFrom AND @DateTo
)t
WHERE RowNum IN (1, 2)

这样代码更简洁，执行效率也更高。

内容的提问来源于stack exchange，提问作者Bob