bl/help/三主宠查询.sql

-- 删除：每个多余精灵组中除了最早创建的其余记录
WITH pet_group_mapping AS (
SELECT 
id,
player_id,
-- 核心修正：PARTITION BY中直接写分组逻辑
ROW_NUMBER() OVER (
PARTITION BY 
player_id,
CASE  -- 3个一组的分组逻辑
WHEN (data->>'ID')::INT BETWEEN 1 AND 3 THEN 'group_1_3'
WHEN (data->>'ID')::INT BETWEEN 4 AND 6 THEN 'group_4_6'
WHEN (data->>'ID')::INT BETWEEN 7 AND 9 THEN 'group_7_9'
WHEN (data->>'ID')::INT BETWEEN 301 AND 303 THEN 'group_301_303'
WHEN (data->>'ID')::INT BETWEEN 304 AND 306 THEN 'group_304_306'
WHEN (data->>'ID')::INT BETWEEN 307 AND 309 THEN 'group_307_309'
END
ORDER BY "createTime" ASC
) AS rn,
-- 定义pet_group用于筛选多余组
CASE 
WHEN (data->>'ID')::INT BETWEEN 1 AND 3 THEN 'group_1_3'
WHEN (data->>'ID')::INT BETWEEN 4 AND 6 THEN 'group_4_6'
WHEN (data->>'ID')::INT BETWEEN 7 AND 9 THEN 'group_7_9'
WHEN (data->>'ID')::INT BETWEEN 301 AND 303 THEN 'group_301_303'
WHEN (data->>'ID')::INT BETWEEN 304 AND 306 THEN 'group_304_306'
WHEN (data->>'ID')::INT BETWEEN 307 AND 309 THEN 'group_307_309'
END AS pet_group
FROM "player_pet"
WHERE deleted_at IS NULL
),
excess_groups AS (
SELECT player_id, pet_group
FROM pet_group_mapping
WHERE pet_group IS NOT NULL
GROUP BY player_id, pet_group
HAVING COUNT(*) > 1
)
DELETE FROM "player_pet"
WHERE id IN (
SELECT pgm.id
FROM pet_group_mapping pgm
INNER JOIN excess_groups eg ON pgm.player_id = eg.player_id AND pgm.pet_group = eg.pet_group
WHERE pgm.rn > 1
);


//删除多余的异常融合精灵
DELETE FROM "player_pet" pp
WHERE
  pp.deleted_at IS NULL
  AND pp.is_vip = 0
  AND (pp.data->>'OldCatchTime')::BIGINT != 0
  AND NOT EXISTS (
    SELECT 1
    FROM config_fusion_pet cfp
    WHERE (pp.data->>'ID')::INT BETWEEN cfp.result_pet_id AND cfp.result_pet_id + 2
  );