Oracle SQL查询需求：基于REPORT-B筛选REPORT-A的最小Followup Date

现有表数据 #

REPORT-A表 #

************************************************
Caseno  Followup Date       REPORT_VERSION
***************************************************
C1    26-JAN-22 20:17:54    7
C1    21-FEB-22 18:43:31    8
C1    21-FEB-22 18:44:37    9
C1    21-MAR-22 20:44:37    10
C1    22-MAR-22 17:56:59    11
C1   22-MAR-22 18:45:18     12
C1    24-MAR-22 00:51:12    13

REPORT-B表 #

************************************************
Caseno  Sub Date        REPORT_VERSION
***************************************************
C1     27-JAN-22 00:00:00   7
C1     24-FEB-22 00:00:00   9
C1     24-MAR-22 00:00:00   13

查询需求 #

通过Caseno关联两张表，筛选满足以下条件的记录：

• REPORT_B.REPORT_VERSION >= REPORT_A.REPORT_VERSION
• REPORT_B.Sub Date >= REPORT_A.Followup Date

为每条REPORT-B记录，匹配对应的最小REPORT_A.Followup Date，并返回关联的REPORT_A版本号等信息。

期望输出 #

*************************************************************************************************
A.Caseno   B.Sub Date       B.REPORT_VERSION   A.REPORT_VERSION        A.Followup_Date
**************************************************************************************************
C1         27-JAN-22 00:00:00      7           7                       26-JAN-22 20:17:54
C1         24-FEB-22 00:00:00      9           8                       21-FEB-22 18:43:31
C1         24-MAR-22 00:00:00      13          10                      21-MAR-22 20:44:37

实现SQL #

方案一：子查询关联 #

先为每条REPORT-B记录找到符合条件的最小Followup Date，再关联REPORT_A表获取对应版本信息：

SELECT 
    a.Caseno AS `A.Caseno`,
    b.Sub_Date AS `B.Sub Date`,
    b.REPORT_VERSION AS `B.REPORT_VERSION`,
    a.REPORT_VERSION AS `A.REPORT_VERSION`,
    a.Followup_Date AS `A.Followup_Date`
FROM REPORT_B b
JOIN REPORT_A a 
    ON b.Caseno = a.Caseno
    AND b.REPORT_VERSION >= a.REPORT_VERSION
    AND b.Sub_Date >= a.Followup_Date
WHERE a.Followup_Date = (
    SELECT MIN(a2.Followup_Date)
    FROM REPORT_A a2
    WHERE a2.Caseno = b.Caseno
    AND b.REPORT_VERSION >= a2.REPORT_VERSION
    AND b.Sub_Date >= a2.Followup_Date
)
ORDER BY b.Sub_Date;

方案二：窗口函数实现 #

用窗口函数直接计算分组内的最小Followup Date，再筛选匹配记录：

WITH filtered_records AS (
    SELECT 
        b.Caseno AS b_caseno,
        b.Sub_Date AS b_sub_date,
        b.REPORT_VERSION AS b_version,
        a.REPORT_VERSION AS a_version,
        a.Followup_Date AS a_followup,
        MIN(a.Followup_Date) OVER (PARTITION BY b.Caseno, b.Sub_Date) AS min_followup
    FROM REPORT_B b
    LEFT JOIN REPORT_A a 
        ON b.Caseno = a.Caseno
        AND b.REPORT_VERSION >= a.REPORT_VERSION
        AND b.Sub_Date >= a.Followup_Date
)
SELECT 
    b_caseno AS `A.Caseno`,
    b_sub_date AS `B.Sub Date`,
    b_version AS `B.REPORT_VERSION`,
    a_version AS `A.REPORT_VERSION`,
    a_followup AS `A.Followup_Date`
FROM filtered_records
WHERE a_followup = min_followup
ORDER BY b_sub_date;

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