如何按Id分组筛选特定键值组合并插入数据至MyPets表

现有PetsTable表结构及数据 #

需求说明 #

需将符合以下条件的数据插入新表MyPets：

• 按Id分组；
• 仅保留组内同时存在(key=10且value=5)、(key=8且value=4)、(key=6且value=3)的分组；
• 若组内存在key=9，则标记hasFee=1，否则标记hasFee=0。

预期MyPets表结构及数据 #

SQL实现方案 #

方案一：GROUP BY结合条件聚合 #

-- 先创建MyPets表（若未创建）
CREATE TABLE IF NOT EXISTS MyPets (
    Id INT,
    Type VARCHAR(50),
    hasFee INT
);

-- 插入符合条件的数据
INSERT INTO MyPets (Id, Type, hasFee)
SELECT 
    Id,
    MAX(Type) AS Type, -- 同Id下Type一致，用MAX/MIN均可
    CASE WHEN COUNT(CASE WHEN `key` = 9 THEN 1 END) > 0 THEN 1 ELSE 0 END AS hasFee
FROM PetsTable
GROUP BY Id
HAVING 
    COUNT(CASE WHEN `key` = 10 AND value = 5 THEN 1 END) > 0
    AND COUNT(CASE WHEN `key` = 8 AND value = 4 THEN 1 END) > 0
    AND COUNT(CASE WHEN `key` = 6 AND value = 3 THEN 1 END) > 0;

方案二：窗口函数筛选分组 #

如果需要更灵活的逻辑扩展，可使用窗口函数先标记分组状态，再插入数据：

CREATE TABLE IF NOT EXISTS MyPets (
    Id INT,
    Type VARCHAR(50),
    hasFee INT
);

WITH GroupCheck AS (
    SELECT 
        *,
        MAX(CASE WHEN `key` = 10 AND value = 5 THEN 1 ELSE 0 END) OVER (PARTITION BY Id) AS has_10_5,
        MAX(CASE WHEN `key` = 8 AND value = 4 THEN 1 ELSE 0 END) OVER (PARTITION BY Id) AS has_8_4,
        MAX(CASE WHEN `key` = 6 AND value = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY Id) AS has_6_3,
        MAX(CASE WHEN `key` = 9 THEN 1 ELSE 0 END) OVER (PARTITION BY Id) AS has_fee_flag
    FROM PetsTable
)
INSERT INTO MyPets (Id, Type, hasFee)
SELECT DISTINCT
    Id,
    Type,
    has_fee_flag AS hasFee
FROM GroupCheck
WHERE has_10_5 = 1 AND has_8_4 = 1 AND has_6_3 = 1;

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