优化SQL查询：为ID分配最常见Item，简化双CTE连接

刚好我之前也处理过类似需求，用窗口函数就能把原来的双CTE写法简化成单嵌套查询，既简洁又高效，完全不用额外的连接操作。

首先先补全你的测试表脚本（加了一些示例数据方便验证效果）：

IF OBJECT_ID('tempdb..#Test') IS NOT NULL DROP TABLE #Test
CREATE TABLE #Test (
    [ID] INT,
    [Item] VARCHAR(20)
)
INSERT #Test VALUES 
(100, 'Apple'),
(100, 'Apple'),
(100, 'Banana'),
(200, 'Orange'),
(200, 'Banana'),
(200, 'Banana'),
(300, 'Grape'),
(300, 'Grape'),
(300, 'Mango'),
(300, 'Mango');

核心优化查询（单SELECT嵌套） #

这个写法直接在子查询里完成分组统计+排名，外层取排名第一的结果：

SELECT ID, Item AS MostFrequentItem
FROM (
    SELECT 
        ID, 
        Item,
        -- 按ID分组，每个Item按出现次数降序排名，相同次数随机取一个
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY COUNT(*) DESC) AS rn
    FROM #Test
    GROUP BY ID, Item
) t
WHERE rn = 1;

处理并列情况（多个Item出现次数相同） #

如果你的场景中允许同一个ID有多个出现次数相同的最常见Item（比如测试数据里的ID=300，Grape和Mango都出现2次），可以把ROW_NUMBER()换成RANK()，这样会返回所有并列第一的结果：

SELECT ID, Item AS MostFrequentItem
FROM (
    SELECT 
        ID, 
        Item,
        -- 相同出现次数的Item会获得相同排名，全部保留
        RANK() OVER (PARTITION BY ID ORDER BY COUNT(*) DESC) AS rn
    FROM #Test
    GROUP BY ID, Item
) t
WHERE rn = 1;

为什么这个写法更优？ #

• 避免了双CTE的连接操作，减少了查询的复杂度
• 只做了一次分组统计和排名，执行效率更高
• 逻辑清晰，子查询里完成所有统计逻辑，外层只负责筛选结果

内容的提问来源于stack exchange，提问作者TheGuyThatDoesn'tKnowMuch