如何统计明细表中的唯一选项组合？SQL实现方案问询

我来帮你搞定这个统计选项组合次数的问题！你已经摸到门道了，你的查询思路是对的，只需要调整几个关键细节就能得到想要的结果。

核心思路拆解 #

首先我们要解决两个核心问题：

1. 确保相同选项组合的字符串格式完全一致（比如A+B和B+A要被识别为同一个组合）
2. 把组合字符串拆分成你需要的多列格式，同时统计出现次数

步骤1：生成统一格式的选项组合字符串 #

首先要给每个商品生成排序后的选项拼接字符串——这一步很重要，否则同一个组合因为选项存储顺序不同会被当成不同的组合。同时要给每个商品只生成一行组合记录，避免重复统计。

WITH ItemCombos AS (
    SELECT 
        Item,
        -- 按选项排序后拼接，保证相同组合的字符串一致
        STUFF((
            SELECT ',' + [Option] 
            FROM itemDetail a 
            WHERE a.Item = b.Item 
            ORDER BY [Option]
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Combo
    FROM itemDetail b
    GROUP BY Item  -- 每个商品只生成一行组合记录
)

步骤2：统计组合出现次数并拆分到多列 #

接下来我们基于上面的组合结果，统计次数，再把逗号分隔的组合拆分成Option 1/2/3列：

WITH ItemCombos AS (
    SELECT 
        Item,
        STUFF((
            SELECT ',' + [Option] 
            FROM itemDetail a 
            WHERE a.Item = b.Item 
            ORDER BY [Option]
            FOR XML PATH(''), TYPE
        ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Combo
    FROM itemDetail b
    GROUP BY Item
),
ComboCounts AS (
    SELECT 
        Combo,
        COUNT(*) AS TotalCount
    FROM ItemCombos
    GROUP BY Combo
)
SELECT
    -- 提取第一个选项
    CASE WHEN CHARINDEX(',', Combo) > 0 THEN LEFT(Combo, CHARINDEX(',', Combo)-1) ELSE Combo END AS [Option 1],
    -- 提取第二个选项
    CASE 
        WHEN CHARINDEX(',', Combo, CHARINDEX(',', Combo)+1) > 0 
        THEN SUBSTRING(Combo, CHARINDEX(',', Combo)+1, CHARINDEX(',', Combo, CHARINDEX(',', Combo)+1)-CHARINDEX(',', Combo)-1)
        ELSE CASE WHEN CHARINDEX(',', Combo) > 0 THEN SUBSTRING(Combo, CHARINDEX(',', Combo)+1, LEN(Combo)) ELSE '' END
    END AS [Option 2],
    -- 提取第三个选项
    CASE WHEN CHARINDEX(',', Combo, CHARINDEX(',', Combo)+1) > 0 
         THEN SUBSTRING(Combo, CHARINDEX(',', Combo, CHARINDEX(',', Combo)+1)+1, LEN(Combo))
         ELSE '' END AS [Option 3],
    TotalCount AS [Count]
FROM ComboCounts
ORDER BY TotalCount DESC, Combo

更灵活的SQL Server 2016+版本 #

如果你的数据库是SQL Server 2016及以上，可以用STRING_SPLIT结合PIVOT来处理，扩展性更强（比如选项超过3个时，只需要调整PIVOT里的数字即可）：

WITH ItemCombos AS (
    SELECT 
        ic.Item,
        s.Value AS OptionVal,
        ROW_NUMBER() OVER (PARTITION BY ic.Item ORDER BY s.Value) AS OptionNum
    FROM (
        SELECT DISTINCT Item
        FROM itemDetail
    ) ic
    CROSS APPLY STRING_SPLIT(
        (SELECT ',' + [Option] FROM itemDetail a WHERE a.Item = ic.Item ORDER BY [Option] FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),
        ','
    ) s
),
PivotedCombos AS (
    SELECT 
        Item,
        [1] AS [Option 1],
        [2] AS [Option 2],
        [3] AS [Option 3]
    FROM ItemCombos
    PIVOT (
        MAX(OptionVal) FOR OptionNum IN ([1], [2], [3])
    ) p
)
SELECT 
    [Option 1],
    [Option 2],
    [Option 3],
    COUNT(*) AS [Count]
FROM PivotedCombos
GROUP BY [Option 1], [Option 2], [Option 3]
ORDER BY [Count] DESC, [Option 1], [Option 2], [Option 3]

关键注意点 #

• 排序选项：一定要在拼接时按Option排序，否则A+B和B+A会被当成不同组合，统计结果会出错
• GROUP BY Item：你的原始查询没有这一步，导致同一个商品会生成多行相同的组合字符串，统计的Count会是选项数量而不是商品数量，这是核心问题

运行上面的代码后，就能得到你想要的结果：

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