SQL中MAX()多匹配结果的平局处理方案问询

现有数据表结构及数据如下：

需实现：

1. 获取最近录入的date_of_service，优先级规则：先取最大的date_of_service，若有多个匹配则取最大的data_entry_date，仍有匹配则取最大的data_entry_time，最终目标行是date_of_service = 02/04/2022，data_entry_date = 02/05/2022，data_entry_time = 0900
2. 将该结果左连接到主表（假设主表名为main_table，关联字段为client_id, program_id, provider_id）

遇到的问题：

• 现有查询在MAX(date_of_service)存在多个匹配时返回多行
• 使用TOP(1)的查询返回date_of_service为null
• 数据库不支持窗口函数（OVER、PARTITION）及COALESCE函数，使用DBeaver 22.2.0测试

由于不支持窗口函数，可通过多层嵌套子查询逐步筛选出符合优先级的唯一行，再进行左连接：

方案1：使用WITH子句（若数据库支持） #

-- 先筛选出优先级最高的目标行
WITH target_row AS (
    SELECT 
        client_id, 
        program_id, 
        provider_id, 
        date_of_service, 
        data_entry_date, 
        data_entry_time
    FROM your_table
    WHERE date_of_service = (
        SELECT MAX(date_of_service) FROM your_table
    )
    AND data_entry_date = (
        SELECT MAX(data_entry_date) 
        FROM your_table
        WHERE date_of_service = (SELECT MAX(date_of_service) FROM your_table)
    )
    AND data_entry_time = (
        SELECT MAX(data_entry_time) 
        FROM your_table
        WHERE date_of_service = (SELECT MAX(date_of_service) FROM your_table)
        AND data_entry_date = (
            SELECT MAX(data_entry_date) 
            FROM your_table
            WHERE date_of_service = (SELECT MAX(date_of_service) FROM your_table)
        )
    )
)
-- 左连接到主表
SELECT 
    mt.*,
    tr.date_of_service AS latest_date_of_service,
    tr.data_entry_date AS latest_data_entry_date,
    tr.data_entry_time AS latest_data_entry_time
FROM main_table mt
LEFT JOIN target_row tr 
    ON mt.client_id = tr.client_id
    AND mt.program_id = tr.program_id
    AND mt.provider_id = tr.provider_id;

方案2：纯子查询嵌套（兼容不支持WITH的数据库） #

SELECT 
    mt.*,
    tr.date_of_service AS latest_date_of_service,
    tr.data_entry_date AS latest_data_entry_date,
    tr.data_entry_time AS latest_data_entry_time
FROM main_table mt
LEFT JOIN (
    SELECT 
        client_id, 
        program_id, 
        provider_id, 
        date_of_service, 
        data_entry_date, 
        data_entry_time
    FROM your_table
    WHERE date_of_service = (
        SELECT MAX(date_of_service) FROM your_table
    )
    AND data_entry_date = (
        SELECT MAX(data_entry_date) 
        FROM your_table
        WHERE date_of_service = (SELECT MAX(date_of_service) FROM your_table)
    )
    AND data_entry_time = (
        SELECT MAX(data_entry_time) 
        FROM your_table
        WHERE date_of_service = (SELECT MAX(date_of_service) FROM your_table)
        AND data_entry_date = (
            SELECT MAX(data_entry_date) 
            FROM your_table
            WHERE date_of_service = (SELECT MAX(date_of_service) FROM your_table)
        )
    )
) tr 
    ON mt.client_id = tr.client_id
    AND mt.program_id = tr.program_id
    AND mt.provider_id = tr.provider_id;

说明： #

• 替换your_table为实际的数据表名，main_table为你的主表名
• 多层子查询依次按优先级筛选：先锁定最大的date_of_service，再在该范围内找最大的data_entry_date，最后在该范围内找最大的data_entry_time，确保最终只返回唯一符合要求的行
• 左连接部分保留主表所有数据，同时关联上筛选出的最新录入记录字段

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