如何在SQL中筛选出行数≥2的最大INPUT_DATE对应数据

需求：从表中筛选出符合过滤条件的最大INPUT_DATE，且该日期下至少存在2条数据，返回该日期的所有行。

示例数据 #

预期结果 #

原查询的问题 #

你写的查询直接锁定了过滤条件下的最大INPUT_DATE（示例中是2022-10-25），但这个日期只有1条数据，经过cnt > 1过滤后没有结果。但我们需要的是所有满足行数≥2的日期中最大的那个，而不是直接取全局最大日期。

正确的SQL写法 #

方法1：借助CTE分步处理 #

先统计每个日期的行数，筛选出行数≥2的日期，再取其中最大的日期，最后关联原表获取数据：

WITH date_counts AS (
    SELECT INPUT_DATE, COUNT(*) AS row_count
    FROM [db]
    WHERE VALUE_C = 'something' AND INPUT_DATE <= '2022-10-25'
    GROUP BY INPUT_DATE
    HAVING COUNT(*) >= 2
),
max_valid_date AS (
    SELECT MAX(INPUT_DATE) AS target_date
    FROM date_counts
)
SELECT d.INPUT_DATE, d.VALUE_A, d.VALUE_B
FROM [db] d
JOIN max_valid_date m ON d.INPUT_DATE = m.target_date
WHERE d.VALUE_C = 'something' AND d.INPUT_DATE <= '2022-10-25';

方法2：使用窗口函数一次性处理 #

通过窗口函数计算每个日期的行数，同时给日期按降序排名，最后筛选出排名第一且行数≥2的行：

SELECT INPUT_DATE, VALUE_A, VALUE_B
FROM (
    SELECT 
        INPUT_DATE, VALUE_A, VALUE_B,
        COUNT(*) OVER (PARTITION BY INPUT_DATE) AS cnt,
        RANK() OVER (ORDER BY INPUT_DATE DESC) AS date_rank
    FROM [db]
    WHERE VALUE_C = 'something' AND INPUT_DATE <= '2022-10-25'
) t
WHERE cnt >= 2 AND date_rank = 1;

这两种方法都能实现需求：如果存在满足行数≥2的最大日期，返回该日期的所有行；如果所有日期的行数都不足2，则返回空结果。

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