求优化SQL查询：筛选含至少2个不同COL2的COL1并聚合展示

思路说明 #

先筛选出拥有至少2个不同COL2值的COL1，再针对这些COL1，统计每个COL2对应的COL3总计数，最后拼接成要求的格式输出。

方案一：使用CTE（可读性强） #

WITH eligible_col1 AS (
    SELECT COL1
    FROM your_table
    GROUP BY COL1
    HAVING COUNT(DISTINCT COL2) >= 2
)
SELECT 
    t.COL1,
    CONCAT(t.COL2, '(', SUM(t.COL3), ')') AS COL2_with_count
FROM your_table t
JOIN eligible_col1 e ON t.COL1 = e.COL1
GROUP BY t.COL1, t.COL2
ORDER BY t.COL1;

方案二：使用窗口函数（性能更优，适合大数据量） #

SELECT 
    COL1,
    CONCAT(COL2, '(', SUM(COL3), ')') AS COL2_with_count
FROM (
    SELECT 
        *,
        COUNT(DISTINCT COL2) OVER (PARTITION BY COL1) AS col2_distinct_count
    FROM your_table
) t
WHERE col2_distinct_count >= 2
GROUP BY COL1, COL2
ORDER BY COL1;

补充说明 #

• 替换your_table为你的实际表名；
• 两种方案均能满足需求，窗口函数方案避免了额外的JOIN操作，在数据量较大时性能表现更优；
• 最终结果按COL1排序，每个COL1下展示对应的COL2值及COL3总计数，与目标表结构完全匹配。

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