如何遍历唯一food值，统计其在food_preference中的count总和？

给定原始表Record：

food   | food_preference | count
--------------------------------
burger | [burger]        | 100
burger | [burger, pizza] | 70
pizza  | [burger, pizza] | 130
burger | [burger, corn]  | 25
corn   | [burger, corn]  | 25

需求是遍历所有唯一的food值，统计该food出现在food_preference中的所有count值之和，得到结果：

food   | count
--------------
burger | 350
pizza  | 200
corn   | 50

用户尝试用CASE匹配，但不清楚如何遍历food值，现有代码如下：

WITH Record AS
(
    SELECT * FROM (
        values 
            ('burger', '[burger]', 100), 
            ('burger', '[burger, pizza]', 70), 
            ('pizza', '[burger, pizza]', 130), 
            ('burger', '[burger, corn]', 25), 
            ('corn', '[burger, corn]', 25)
    ) x(food, food_preference, count)
)

SELECT 
    food,
    CASE
        -- How to sum the count value if the food is in the food_preference? 
    END AS total_count
FROM Record

要实现这个需求，不需要依赖CASE做遍历，核心是先提取唯一food集合，再关联原表做匹配求和，具体实现如下：

基础实现（适配大多数SQL方言） #

WITH Record AS
(
    SELECT * FROM (
        values 
            ('burger', '[burger]', 100), 
            ('burger', '[burger, pizza]', 70), 
            ('pizza', '[burger, pizza]', 130), 
            ('burger', '[burger, corn]', 25), 
            ('corn', '[burger, corn]', 25)
    ) x(food, food_preference, count)
)
SELECT 
    unique_food.food,
    SUM(CASE WHEN r.food_preference LIKE CONCAT('%', unique_food.food, '%') THEN r.count ELSE 0 END) AS count
FROM (SELECT DISTINCT food FROM Record) unique_food
CROSS JOIN Record r
GROUP BY unique_food.food
ORDER BY unique_food.food;

逻辑说明 #

• (SELECT DISTINCT food FROM Record) unique_food：提取所有唯一的food值，作为统计的目标集合；
• CROSS JOIN Record r：将每个唯一food与原表所有行关联，确保每个food能检查到所有可能的food_preference；
• CASE WHEN ... THEN r.count ELSE 0 END：判断当前唯一food是否出现在该行的food_preference中，符合条件则累加对应count，否则加0；
• SUM(...) + GROUP BY unique_food.food：按food分组求和，得到最终统计结果。

优化实现（支持数组类型的SQL方言，如PostgreSQL） #

如果你的SQL支持数组操作，可以避免字符串匹配的误判（比如区分burger和burgerking这类相似字符串），优化代码如下：

WITH Record AS
(
    SELECT * FROM (
        values 
            ('burger', ARRAY['burger']::varchar[], 100), 
            ('burger', ARRAY['burger', 'pizza']::varchar[], 70), 
            ('pizza', ARRAY['burger', 'pizza']::varchar[], 130), 
            ('burger', ARRAY['burger', 'corn']::varchar[], 25), 
            ('corn', ARRAY['burger', 'corn']::varchar[], 25)
    ) x(food, food_preference, count)
)
SELECT 
    unique_food.food,
    SUM(CASE WHEN unique_food.food = ANY(r.food_preference) THEN r.count ELSE 0 END) AS count
FROM (SELECT DISTINCT food FROM Record) unique_food
CROSS JOIN Record r
GROUP BY unique_food.food
ORDER BY unique_food.food;

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