PL/SQL查询：同表基于日期的价格对应差值计算求助

原表数据 #

期望结果 #

实际结果 #

当前使用的SQL代码 #

Select a.id,a.date,a.price,b.id,b.date,b.price,(b.price-a.price)
from xyz a,xyz b      -- same table
where a.id = 2
and a.id = b.id
and a.date = to_date('03092020','ddmmyyyy') and b.date = to_date('05092020','ddmmyyyy')
order by a.id,a.date

（注：修正了原代码的todate为Oracle标准TO_DATE，orderby改为ORDER BY）

问题原因 #

当前查询直接关联同一张表的两个日期分组，由于ID=2在两个日期下各有2条记录，会产生2*2=4条笛卡尔积结果，而非一一对应的2条。

解决方案 #

给每个ID+日期分组内的记录添加行号，然后通过ID和行号进行关联，确保同一分组内的第N条记录只和另一日期分组的第N条记录计算差值。

修正后的SQL代码 #

WITH date_group AS (
    SELECT 
        id,
        date,
        price1,
        ROW_NUMBER() OVER(PARTITION BY id, date ORDER BY price1) AS rn
    FROM xyz
)
SELECT 
    a.id AS "A.id",
    a.date AS "date 1",
    a.price1 AS "price 1",
    b.id AS "B.ID",
    b.date AS "date 2",
    b.price1 AS "price 2",
    (b.price1 - a.price1) AS diff
FROM date_group a
JOIN date_group b 
    ON a.id = b.id 
    AND a.rn = b.rn
WHERE 
    a.id = 2
    AND a.date = TO_DATE('03092020','ddmmyyyy')
    AND b.date = TO_DATE('05092020','ddmmyyyy')
ORDER BY a.id, a.date;

代码说明 #

1. CTE date_group：给每个id+date分组内的记录按price1排序并生成行号rn，确保同一分组内的每条记录有唯一的序号。
2. 关联条件：除了id相等，还添加a.rn = b.rn，保证两个日期分组内的记录一一对应。
3. 筛选条件：保留需要的ID和日期范围，最终得到期望的一一对应差值结果。

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