基于MAX和MIN日期筛选SQL行的技术问询

表结构说明 #

• 表名：hhmonthlyprogress（以下SQL中用别名mprog指代）
• prgid：每条记录的唯一标识
• hhid：理论上每月唯一，但实际存在重复记录
• frdate：记录的录入日期
• assetdate：可为\N，与liftranche1字段关联
• sifdate：逻辑与assetdate一致，对应sif字段

现有查询及问题 #

原查询试图筛选每个hhid对应的最大prgid记录，但无法满足实际需求：

SELECT hhid, frdate, DATE(sifdate) AS sif_date, sif,  
DATE(assetdate) AS lif_date, lif, liftranch1,
 liftranch2, lgaf
FROM mprog
WHERE prgid 
IN (
SELECT MAX(prgid) FROM mprog
GROUP BY hhid)

期望输出规则 #

需要为每个唯一的hhid返回以下内容：

• 该hhid对应**最大frdate**记录的prgid
• 该hhid所有记录中的最小assetdate
• 有效的liftranche1、liftranche2、lgaf值
• 该hhid所有记录中的**最小sifdate**及对应的sif值

示例输出 #

prgid    hhid   max(frdate)       min(assetdate)     liftranche1 liftranch2  lgaf    min(sifdate)     sif
1429489 22     10/08/2022 13:16   03/08/2018 20:30   20000       0           7000    08/03/2020 18:30 10000
1503111 24     09/11/2022 15:46   \N                 \N          \N          \N      \N               \N
1390179 69     02/11/2022 14:01   14/03/2019 18:30   20000       0           7000    09/02/2020 18:30 10000
1477822 10     07/11/2022 14:32   \N                 \N          \N          \N      09/03/2020 13:00 10000

解决方案SQL #

通过CTE先计算每个hhid的关键聚合值，再关联主表获取对应字段：

WITH hh_agg AS (
    SELECT 
        hhid,
        MAX(frdate) AS max_frdate,
        MIN(assetdate) AS min_assetdate,
        MIN(sifdate) AS min_sifdate
    FROM mprog
    GROUP BY hhid
),
frdate_prg AS (
    SELECT 
        hhid,
        prgid,
        liftranche1,
        liftranch2,
        lgaf
    FROM mprog
    WHERE (hhid, frdate) IN (
        SELECT hhid, MAX(frdate) FROM mprog GROUP BY hhid
    )
),
sif_data AS (
    SELECT 
        hhid,
        sif
    FROM mprog
    WHERE (hhid, sifdate) IN (
        SELECT hhid, MIN(sifdate) FROM mprog GROUP BY hhid
    )
)
SELECT 
    fp.prgid,
    a.hhid,
    a.max_frdate,
    a.min_assetdate,
    fp.liftranche1,
    fp.liftranch2,
    fp.lgaf,
    a.min_sifdate,
    sd.sif
FROM hh_agg a
JOIN frdate_prg fp ON a.hhid = fp.hhid
LEFT JOIN sif_data sd ON a.hhid = sd.hhid;

逻辑说明 #

1. hh_agg：计算每个hhid的最大录入日期、最小资产日期、最小sif日期
2. frdate_prg：筛选每个hhid对应最大录入日期的记录，获取prgid和资产相关字段
3. sif_data：筛选每个hhid对应最小sif日期的记录，获取sif值
4. 关联三个CTE，输出符合需求的结果

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