如何基于多列在SQL中统计重复值：多色配件订单统计

原SQL的问题 #

1. IN子查询返回多列数据，不符合语法要求，IN仅能匹配单列值，会直接引发语法错误。
2. 统计逻辑有误：外层COUNT(Device)会重复统计同一设备的多条记录，无法得到准确的多色订购产品数量。

修正后的SQL写法 #

写法1：子查询筛选目标组合后统计

SELECT 
    `Major category`,
    COUNT(DISTINCT CONCAT(`Invoice`, '|', `Device`)) AS multi_color_product_count
FROM `tab`
WHERE (`Invoice`, `Major category`, `Device`) IN (
    SELECT 
        `Invoice`,
        `Major category`,
        `Device`
    FROM `tab`
    GROUP BY `Invoice`, `Major category`, `Device`
    HAVING COUNT(DISTINCT `color`) > 1
)
GROUP BY `Major category`;

写法2：CTE（公共表表达式）清晰展示逻辑

WITH multi_color_devices AS (
    SELECT 
        `Invoice`,
        `Major category`,
        `Device`
    FROM `tab`
    GROUP BY `Invoice`, `Major category`, `Device`
    HAVING COUNT(DISTINCT `color`) > 1
)
SELECT 
    mcd.`Major category`,
    COUNT(DISTINCT CONCAT(mcd.`Invoice`, '|', mcd.`Device`)) AS multi_color_product_count
FROM multi_color_devices mcd
GROUP BY mcd.`Major category`;

逻辑说明 #

1. 先通过子查询/CTE筛选出同一发票、同一主类别、同一设备下存在多种颜色的组合（用COUNT(DISTINCT color) >1确保颜色数量大于1）。
2. 外层查询按Major category分组，用COUNT(DISTINCT CONCAT(Invoice, '|', Device))统计每个主类别下，符合多色订购的（发票+设备）唯一组合数，避免重复计数。

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