如何修改SQL查询以获取Microsoft SQL中最长最热门的商品组合？

原查询只能处理两个商品的组合，要实现获取最长且出现次数最多的商品组合，我们可以用递归CTE（公共表表达式）来生成所有可能的商品组合，再统计筛选。下面是针对Microsoft SQL Server的具体实现方案：

完整查询代码 #

WITH DateArticles AS (
    -- 第一步：整理每个日期的唯一商品列表，并给商品排序编号
    SELECT 
        ReportDate,
        ArticleCode,
        -- 给同一日期内的商品按编码排序，生成序号（避免重复组合）
        ROW_NUMBER() OVER (PARTITION BY ReportDate ORDER BY ArticleCode) AS rn,
        COUNT(*) OVER (PARTITION BY ReportDate) AS totalArticles
    FROM dbo.Outbound
    GROUP BY ReportDate, ArticleCode -- 去重同一日期内的重复商品
),
CombinationCTE AS (
    -- 递归基础：单个商品作为起始（后续会筛选掉长度为1的组合）
    SELECT 
        ReportDate,
        CAST(ArticleCode AS VARCHAR(MAX)) AS Combination,
        rn,
        1 AS CombinationLength
    FROM DateArticles
    UNION ALL
    -- 递归生成更长的组合：只添加当前组合最后一个商品序号更大的商品，避免重复组合（比如A+B和B+A不会重复生成）
    SELECT 
        ca.ReportDate,
        cc.Combination + ',' + ca.ArticleCode AS Combination,
        ca.rn,
        cc.CombinationLength + 1 AS CombinationLength
    FROM CombinationCTE cc
    INNER JOIN DateArticles ca 
        ON ca.ReportDate = cc.ReportDate 
        AND ca.rn > cc.rn
)
-- 统计每个组合的出现次数，筛选出长度≥2的组合，按长度降序、次数降序排序
SELECT TOP 1 WITH TIES
    Combination,
    CombinationLength,
    COUNT(*) AS OccurrenceCount
FROM CombinationCTE
WHERE CombinationLength >= 2 -- 排除单个商品的情况，只保留组合
GROUP BY Combination, CombinationLength
ORDER BY CombinationLength DESC, OccurrenceCount DESC;

关键逻辑说明 #

1. DateArticles CTE：

   • 先对每个日期的商品去重（同一日期同一商品多次出库只算一次）
   • 给每个日期内的商品按编码排序并生成序号，这一步是为了避免生成重复的组合（比如A+B和B+A会被视为同一个组合，只生成一次）

2. CombinationCTE递归生成组合：

   • 从单个商品开始，逐步递归添加序号更大的商品，生成所有可能的商品组合（长度从1到该日期的商品总数）
   • 用序号限制添加的商品，确保组合内的商品按编码顺序排列，彻底避免重复组合

3. 统计与筛选：

   • 只统计长度≥2的组合（符合“组合”的定义）
   • 用TOP 1 WITH TIES可以返回所有最长且出现次数最多的组合（如果有多个组合长度相同且次数并列第一，都会被返回）

注意事项 #

• 如果你的Outbound表数据量很大，递归生成所有组合可能会影响性能。可以在递归CTE里加一个条件（比如CombinationLength < 5）来限制生成的组合最大长度，根据业务需求调整。
• 组合的分隔符（代码里用的逗号）可以换成其他字符，只要不会和ArticleCode里的字符冲突就行。

内容的提问来源于stack exchange，提问作者Barnabás Kriszt