SQL分组存在重复最小StartDate时取最新起始日期的查询方案

需求拆解 #

先明确核心实现规则：

• 按LicenseID分组，每个分组最终仅返回1条记录
• 常规场景取分组内最小的StartDate作为结果
• 特殊判定：如果分组内最早的StartDate存在2条及以上重复记录，则最终返回的StartDate取分组内最新的StartDate，同时匹配该日期对应的EndDate
• 测试样例校验：LicenseID=100存在2条StartDate='2000/4/3'的最早重复记录，最终输出StartDate='2018/12/18'、EndDate='2021/2/16'

可直接运行的代码 #

以下写法兼容MySQL 8.x、SQL Server、PostgreSQL等主流数据库，逻辑分层清晰适合新手理解，把表名替换成你的实际业务表名即可运行：

WITH group_base_stat AS (
    SELECT
        LicenseID,
        MIN(StartDate) AS min_start,
        MAX(StartDate) AS max_start
    FROM your_license_table
    GROUP BY LicenseID
),
group_min_count AS (
    SELECT
        s.LicenseID,
        s.min_start,
        s.max_start,
        COUNT(t.StartDate) AS min_start_repeat_count
    FROM group_base_stat s
    LEFT JOIN your_license_table t
        ON s.LicenseID = t.LicenseID AND t.StartDate = s.min_start
    GROUP BY s.LicenseID, s.min_start, s.max_start
),
final_target AS (
    SELECT
        LicenseID,
        CASE WHEN min_start_repeat_count >= 2 THEN max_start ELSE min_start END AS target_start
    FROM group_min_count
)
SELECT
    f.LicenseID,
    f.target_start AS StartDate,
    t.EndDate
FROM final_target f
LEFT JOIN your_license_table t
    ON f.LicenseID = t.LicenseID AND t.StartDate = f.target_start;

如果你使用MySQL 5.x等不支持CTE语法的老版本，可以改用嵌套子查询实现，逻辑完全一致：

SELECT
    f.LicenseID,
    f.target_start AS StartDate,
    t.EndDate
FROM (
    SELECT
        s.LicenseID,
        CASE WHEN (SELECT COUNT(*) FROM your_license_table c WHERE c.LicenseID = s.LicenseID AND c.StartDate = s.min_start) >=2 
            THEN s.max_start 
            ELSE s.min_start 
        END AS target_start
    FROM (
        SELECT LicenseID, MIN(StartDate) AS min_start, MAX(StartDate) AS max_start
        FROM your_license_table
        GROUP BY LicenseID
    ) s
) f
LEFT JOIN your_license_table t
    ON f.LicenseID = t.LicenseID AND t.StartDate = f.target_start;

逻辑说明 #

• 代码先按LicenseID分组算出每个组的最早、最晚StartDate，再统计最早StartDate在组内的重复次数
• 用CASE分支做判定：重复次数>=2就取最晚的StartDate，否则取最早的StartDate
• 最后关联原表拿到目标StartDate对应的EndDate即可，针对你给出的LicenseID=100的测试样例，会准确返回预期的2018/12/18、2021/2/16的结果

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