SQL Server如何按category分组查询各列出现频次最高的值

实现思路 #

你原有的单列统计逻辑是正确的，拓展到多列时可以对每个目标列单独统计对应category分组下的频次，取排序第一的最高频值即可，频次相同时SQL会默认取第一条，符合你“频次相同任选其一”的要求。

通用实现方案（兼容SQL Server 2008及以上版本） #

使用CROSS APPLY实现，代码简洁易维护，新增统计列时只需新增对应逻辑即可：

SELECT DISTINCT
    d.category,
    n1.name_val AS name1,
    n2.name_val AS name2,
    n3.name_val AS name3,
    n4.name_val AS name4,
    n5.name_val AS name5
FROM data d
-- 统计name1最高频值
CROSS APPLY (
    SELECT TOP 1 name1 AS name_val
    FROM data
    WHERE category = d.category
    GROUP BY name1
    ORDER BY COUNT(*) DESC
) n1
-- 统计name2最高频值
CROSS APPLY (
    SELECT TOP 1 name2 AS name_val
    FROM data
    WHERE category = d.category
    GROUP BY name2
    ORDER BY COUNT(*) DESC
) n2
-- 统计name3最高频值
CROSS APPLY (
    SELECT TOP 1 name3 AS name_val
    FROM data
    WHERE category = d.category
    GROUP BY name3
    ORDER BY COUNT(*) DESC
) n3
-- 统计name4最高频值
CROSS APPLY (
    SELECT TOP 1 name4 AS name_val
    FROM data
    WHERE category = d.category
    GROUP BY name4
    ORDER BY COUNT(*) DESC
) n4
-- 统计name5最高频值
CROSS APPLY (
    SELECT TOP 1 name5 AS name_val
    FROM data
    WHERE category = d.category
    GROUP BY name5
    ORDER BY COUNT(*) DESC
) n5

窗口函数实现方案 #

如果更习惯用CTE+窗口函数的写法，也可以用如下逻辑，执行效率和上面的方案基本一致：

WITH
-- 统计name1排名
stat1 AS (
    SELECT category, name1, ROW_NUMBER() OVER(PARTITION BY category ORDER BY COUNT(*) DESC) rn
    FROM data GROUP BY category, name1
),
-- 统计name2排名
stat2 AS (
    SELECT category, name2, ROW_NUMBER() OVER(PARTITION BY category ORDER BY COUNT(*) DESC) rn
    FROM data GROUP BY category, name2
),
-- 统计name3排名
stat3 AS (
    SELECT category, name3, ROW_NUMBER() OVER(PARTITION BY category ORDER BY COUNT(*) DESC) rn
    FROM data GROUP BY category, name3
),
-- 统计name4排名
stat4 AS (
    SELECT category, name4, ROW_NUMBER() OVER(PARTITION BY category ORDER BY COUNT(*) DESC) rn
    FROM data GROUP BY category, name4
),
-- 统计name5排名
stat5 AS (
    SELECT category, name5, ROW_NUMBER() OVER(PARTITION BY category ORDER BY COUNT(*) DESC) rn
    FROM data GROUP BY category, name5
)
-- 关联所有列的最高频结果
SELECT 
    s1.category, s1.name1, s2.name2, s3.name3, s4.name4, s5.name5
FROM stat1 s1
JOIN stat2 s2 ON s1.category = s2.category
JOIN stat3 s3 ON s1.category = s3.category
JOIN stat4 s4 ON s1.category = s4.category
JOIN stat5 s5 ON s1.category = s5.category
WHERE s1.rn = 1 AND s2.rn =1 AND s3.rn=1 AND s4.rn=1 AND s5.rn=1

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