如何查询同一张pizza表中两个pizza_id的共同配料？

要实现这个需求，核心是先拆分配料字符串找到共同配料，再将共同配料重新拼接后关联回每个披萨ID。以下是不同数据库的具体实现：

MySQL 实现 #

WITH split_toppings AS (
    -- 递归拆分每个披萨的配料为单独行
    SELECT 
        pizza_id,
        SUBSTRING_INDEX(toppings, ',', 1) AS topping,
        SUBSTRING(toppings, LENGTH(SUBSTRING_INDEX(toppings, ',', 1)) + 2) AS remaining_toppings
    FROM pizza
    UNION ALL
    SELECT 
        pizza_id,
        SUBSTRING_INDEX(remaining_toppings, ',', 1) AS topping,
        SUBSTRING(remaining_toppings, LENGTH(SUBSTRING_INDEX(remaining_toppings, ',', 1)) + 2) AS remaining_toppings
    FROM split_toppings
    WHERE remaining_toppings != ''
),
common_toppings AS (
    -- 找出两个披萨共有的配料，拼接成字符串
    SELECT GROUP_CONCAT(topping ORDER BY topping) AS common_list
    FROM split_toppings
    GROUP BY topping
    HAVING COUNT(DISTINCT pizza_id) = 2
)
-- 关联原表，每个披萨ID返回共同配料
SELECT 
    p.pizza_id,
    c.common_list AS toppings
FROM pizza p
CROSS JOIN common_toppings c;

SQL Server 实现 #

WITH split_toppings AS (
    -- 拆分配料为单独行
    SELECT 
        pizza_id,
        value AS topping
    FROM pizza
    CROSS APPLY STRING_SPLIT(toppings, ',')
),
common_toppings AS (
    -- 找出共同配料并拼接
    SELECT STRING_AGG(topping, ',') WITHIN GROUP (ORDER BY topping) AS common_list
    FROM split_toppings
    GROUP BY topping
    HAVING COUNT(DISTINCT pizza_id) = 2
)
SELECT 
    p.pizza_id,
    c.common_list AS toppings
FROM pizza p
CROSS JOIN common_toppings c;

逻辑说明 #

1. 拆分字符串：通过递归（MySQL）或内置拆分函数（SQL Server），将每个披萨的逗号分隔配料拆分成单独的行记录，方便后续对比。
2. 筛选共同配料：按配料分组，统计出现的不同披萨ID数量，数量等于2的就是两个披萨的共同配料。
3. 拼接字符串：将筛选出的共同配料重新拼接成逗号分隔的字符串。
4. 关联输出：将拼接后的共同配料与原表做交叉连接，让每个披萨ID都返回这份共同配料列表。

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