SQL自连接执行机制解析及查询结果不符预期的原因咨询

核心原因：连接条件逻辑写反 #

你的自连接条件c1.Id = c2.Id+1的逻辑是匹配c1的Id比c2大1的记录，也就是把后一天的记录（c1）和前一天的记录（c2）关联起来，这和你预期的“前一天（c1）关联后一天（c2）”完全相反，所以得到的结果顺序和你想要的正好颠倒。

SQL自连接的执行机制 #

自连接本质上是将同一张表视为两个独立的逻辑表（这里的cte c1和cte c2就是两个逻辑副本），数据库会根据你指定的ON条件，遍历两个逻辑表中的记录，找到满足条件的记录对进行关联：

• 当你写c1.Id = c2.Id+1时，数据库会匹配所有c2.Id + 1 = c1.Id的记录对，比如c2.Id=1对应c1.Id=2、c2.Id=2对应c1.Id=3，最终得到后一天记录在前、前一天记录在后的结果。
• 而你预期的是c1为前一天、c2为后一天，所以连接条件应该改为c2.Id = c1.Id + 1，这样就能让c1.Id=1对应c2.Id=2，完全符合你想要的关联关系。

额外提示：CTE中的ORDER BY不生效 #

你在CTE里添加的ORDER BY w1.recordDate不会对后续自连接产生影响，因为CTE返回的是无序结果集（除非搭配LIMIT这类限制行数的子句）。如果需要最终结果按日期排序，应该在SELECT语句末尾添加ORDER BY c1.recordDate。

修正后的查询语句 #

WITH cte AS (SELECT * FROM Weather)
SELECT c1.*, c2.*
FROM cte c1
JOIN cte c2 ON c2.Id = c1.Id + 1
ORDER BY c1.recordDate;

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