如何在查询最大值时获取对应的DATE列数据？

错误原因分析 #

你修改后的查询将TYPE和DATE共同作为分组条件，这会把每个TYPE+DATE组合单独分组，返回的是每个日期的最大次数，而非每个TYPE全局最大次数对应的日期。当同一TYPE下存在多个日期的count_30_days等于最大值时，直接关联会触发"Single-row subquery returns more than one row"错误。

解决方案 #

以下两种方法都能实现需求，可根据是否需要保留多个同最大值的日期选择：

方法一：使用窗口函数（优先推荐）

通过ROW_NUMBER()或RANK()窗口函数，给每个TYPE内的行按count_30_days排序，筛选出排名第一的行：

WITH cte AS 
(
    SELECT
        t.ID, t.TYPE, t.DATE, COUNT(*) AS count_30_days
    FROM 
        ASQ_AFTERSALES.TYPE AS t
    INNER JOIN 
        ASQ_AFTERSALES.TYPE i ON t.DATE >= ADD_DAYS(i.DATE, -30)
                              AND t.DATE <= i.DATE
                              AND t.TYPE = i.TYPE
    GROUP BY 
        t.ID, t.TYPE, t.DATE
),
ranked_data AS (
    SELECT 
        TYPE, DATE, count_30_days,
        -- 若需保留所有同最大值的日期，将ROW_NUMBER替换为RANK()
        ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY count_30_days DESC, DATE DESC) AS row_rank
    FROM cte
)
SELECT 
    TYPE, count_30_days AS max_incidence, DATE
FROM ranked_data
WHERE row_rank = 1;

• ROW_NUMBER()：当同一TYPE有多个日期的次数等于最大值时，仅返回最新日期的行（通过DATE DESC控制）
• RANK()：会返回所有次数等于最大值的日期行

方法二：先求最大值再关联查询

先计算每个TYPE的最大次数，再关联原CTE获取对应的日期：

WITH cte AS 
(
    SELECT
        t.ID, t.TYPE, t.DATE, COUNT(*) AS count_30_days
    FROM 
        ASQ_AFTERSALES.TYPE AS t
    INNER JOIN 
        ASQ_AFTERSALES.TYPE i ON t.DATE >= ADD_DAYS(i.DATE, -30)
                              AND t.DATE <= i.DATE
                              AND t.TYPE = i.TYPE
    GROUP BY 
        t.ID, t.TYPE, t.DATE
),
type_max_counts AS (
    SELECT TYPE, MAX(count_30_days) AS max_incidence
    FROM cte
    GROUP BY TYPE
)
SELECT 
    tmc.TYPE, tmc.max_incidence, c.DATE
FROM type_max_counts tmc
JOIN cte c ON tmc.TYPE = c.TYPE AND tmc.max_incidence = c.count_30_days;

• 若需去重（仅保留每个TYPE的一个日期），可在SELECT后添加DISTINCT，或通过子查询排序取第一。

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