使用CASE语句时如何统计并展示唯一列组合的正确计数？

问题核心 #

你当前的SQL用Item和City原始字段分组，导致即使CASE转换后生成了相同的Purchase字符串，只要原始的Item/City组合不同，就会被分成多个小组，最终出现重复的Purchase行，计数是原始分组的数量，而非转换后合并的总数。

解决方案 #

以下两种方法都能得到你想要的结果：

方法1：直接按转换后的结果分组

如果你的数据库支持在GROUP BY中使用SELECT的别名（如MySQL 8.0+、PostgreSQL），可以直接用Purchase别名分组：

SELECT 
    CONCAT(
        CASE 
            WHEN Item LIKE '%Corolla%' THEN 'Toyota'
            WHEN Item LIKE '%Taco%' THEN 'Toyota'
            WHEN Item LIKE 'F%' THEN 'Ford'
            ELSE Item
        END,
        ' in ',
        CASE
            WHEN City LIKE 'Salt Lake%' THEN 'Utah'
            WHEN City LIKE 'Las%' THEN 'California'
            WHEN City LIKE 'San%' THEN 'California'
            ELSE City
        END
    ) AS Purchase,
    COUNT(*) AS Total
FROM PurchaseOrders
GROUP BY Purchase
ORDER BY Purchase;

如果数据库不支持别名分组，就把两个CASE表达式直接放到GROUP BY里：

SELECT 
    CONCAT(
        CASE 
            WHEN Item LIKE '%Corolla%' THEN 'Toyota'
            WHEN Item LIKE '%Taco%' THEN 'Toyota'
            WHEN Item LIKE 'F%' THEN 'Ford'
            ELSE Item
        END,
        ' in ',
        CASE
            WHEN City LIKE 'Salt Lake%' THEN 'Utah'
            WHEN City LIKE 'Las%' THEN 'California'
            WHEN City LIKE 'San%' THEN 'California'
            ELSE City
        END
    ) AS Purchase,
    COUNT(*) AS Total
FROM PurchaseOrders
GROUP BY 
    CASE 
        WHEN Item LIKE '%Corolla%' THEN 'Toyota'
        WHEN Item LIKE '%Taco%' THEN 'Toyota'
        WHEN Item LIKE 'F%' THEN 'Ford'
        ELSE Item
    END,
    CASE
        WHEN City LIKE 'Salt Lake%' THEN 'Utah'
        WHEN City LIKE 'Las%' THEN 'California'
        WHEN City LIKE 'San%' THEN 'California'
        ELSE City
    END
ORDER BY Purchase;

方法2：子查询先转换再分组

先通过子查询把原始数据转换成Brand和State，再基于这两个字段分组拼接，兼容性更强：

SELECT 
    CONCAT(Brand, ' in ', State) AS Purchase,
    COUNT(*) AS Total
FROM (
    SELECT
        CASE 
            WHEN Item LIKE '%Corolla%' THEN 'Toyota'
            WHEN Item LIKE '%Taco%' THEN 'Toyota'
            WHEN Item LIKE 'F%' THEN 'Ford'
            ELSE Item
        END AS Brand,
        CASE
            WHEN City LIKE 'Salt Lake%' THEN 'Utah'
            WHEN City LIKE 'Las%' THEN 'California'
            WHEN City LIKE 'San%' THEN 'California'
            ELSE City
        END AS State
    FROM PurchaseOrders
) AS TransformedData
GROUP BY Brand, State
ORDER BY Purchase;

预期结果 #

运行以上任意SQL，都会得到正确的统计结果：

Toyota in Utah      5
Ford in California  6

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