如何在SQL中基于最新报告记录执行多表Join并筛选数据？

需求说明 #

• 关联Customer、CustomerReport、ReportStatus三个表
• 获取每个客户最新的reportId（数值最大的记录）
• 筛选报告提交时间早于'2024-01-24'的记录
• 忽略timestamp为null的记录

样例数据 #

Customer表 #

Id  Name
1   ABC
2   DEF
3   GHI
4   XYZ

CustomerReport表 #

customerId  reportId
1           10
1           11
1           12
2           21
2           22
3           30
3           31
4           40
4           41

ReportStatus表 #

reportId timestamp              status
10       2024-01-14 00:11:47    false
11       2024-01-15 00:11:47    false
12       2024-01-24 00:11:47    true
21       2024-01-14 00:11:47    false
22       2024-01-16 00:11:47    false
30       2024-01-16 00:11:47    false
31       2024-01-16 00:11:47    false
40                              false
41                              false

期望输出 #

CustomerId  Name    ReportId TimeStamp              Status
2           DEF     22       2024-01-16 00:11:47    false
3           GHI     31       2024-01-16 00:11:47    false

问题分析 #

• 原查询未按客户筛选最新报告，会返回客户所有符合时间条件的记录
• 尝试的单条最新报告查询仅能返回全局第一条，无法按客户分组获取各自的最新报告

解决方案 #

方法1：使用窗口函数（推荐） #

通过ROW_NUMBER()按客户分组，为每个客户的报告按reportId倒序编号，取编号为1的记录即为最新报告：

SELECT 
    c.id AS CustomerId,
    c.Name,
    cr.reportId AS ReportId,
    rs.timestamp AS TimeStamp,
    rs.status AS Status
FROM Customer c
JOIN CustomerReport cr ON c.id = cr.customerId
JOIN ReportStatus rs ON cr.reportId = rs.reportId
JOIN (
    SELECT 
        customerId,
        reportId,
        ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY reportId DESC) AS rn
    FROM CustomerReport
) latest_cr ON cr.customerId = latest_cr.customerId 
            AND cr.reportId = latest_cr.reportId 
            AND latest_cr.rn = 1
WHERE rs.timestamp < '2024-01-24'
  AND rs.timestamp IS NOT NULL;

方法2：使用分组子查询获取最大reportId #

先分组查询每个客户的最大reportId，再关联回原表获取对应记录：

SELECT 
    c.id AS CustomerId,
    c.Name,
    cr.reportId AS ReportId,
    rs.timestamp AS TimeStamp,
    rs.status AS Status
FROM Customer c
JOIN (
    SELECT customerId, MAX(reportId) AS max_reportId
    FROM CustomerReport
    GROUP BY customerId
) latest_cr ON c.id = latest_cr.customerId
JOIN CustomerReport cr ON latest_cr.customerId = cr.customerId 
                      AND latest_cr.max_reportId = cr.reportId
JOIN ReportStatus rs ON cr.reportId = rs.reportId
WHERE rs.timestamp < '2024-01-24'
  AND rs.timestamp IS NOT NULL;

原查询修正说明 #

两种方案均解决了「按客户筛选最新报告」的核心问题，同时添加rs.timestamp IS NOT NULL条件过滤空时间记录，最终得到符合需求的结果。

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