嘿，这个清理无效积分记录的需求我很熟悉，给你整理了两种实用的SQL方案，还有关键细节和注意事项：

我们的核心思路是：先定位目标赛季内总积分合计为0的选手，再精准删除他们在该赛季所有赛事中的积分记录。以下是适配不同数据库的实现方式：

方法1：使用DELETE + JOIN（适配MySQL、PostgreSQL等） #

假设你要清理的是2020赛季的数据，这种写法直接通过关联表锁定目标记录：

DELETE pt
FROM Players_Tournaments pt
JOIN Tournament t ON pt.Tournament_id = t.id
JOIN Season s ON t.season_id = s.id
WHERE s.year = 2020 -- 指定要清理的赛季年份
AND pt.Player_id IN (
    -- 子查询：找出该赛季总积分为0的选手ID
    SELECT Player_id
    FROM Players_Tournaments pt_sub
    JOIN Tournament t_sub ON pt_sub.Tournament_id = t_sub.id
    JOIN Season s_sub ON t_sub.season_id = s_sub.id
    WHERE s_sub.year = 2020
    GROUP BY Player_id
    HAVING SUM(pt_sub.Points) = 0
);

方法2：使用CTE（公共表达式，适配PostgreSQL、SQL Server等） #

如果你的数据库支持CTE，这种写法逻辑更清晰，可读性更强：

WITH ZeroTotalPlayers AS (
    SELECT Player_id
    FROM Players_Tournaments pt
    JOIN Tournament t ON pt.Tournament_id = t.id
    JOIN Season s ON t.season_id = s.id
    WHERE s.year = 2020 -- 指定目标赛季
    GROUP BY Player_id
    HAVING SUM(pt.Points) = 0
)
DELETE FROM Players_Tournaments pt
USING ZeroTotalPlayers ztp
JOIN Tournament t ON pt.Tournament_id = t.id
JOIN Season s ON t.season_id = s.id
WHERE pt.Player_id = ztp.Player_id
AND s.year = 2020;

关键细节解释 #

• 精准筛选：子查询/CTE通过GROUP BY Player_id计算每个选手在目标赛季的总积分，用HAVING SUM(Points) = 0锁定总积分为0的选手
• 避免误删：删除时再次关联Tournament和Season，确保只清理目标赛季的记录，不会影响其他赛季的数据
• 灵活适配：如果需要按赛季ID筛选，把s.year = 2020改成s.id = [你的赛季ID]即可

必做注意事项 #

1. 先验证再删除：执行删除前一定要先把DELETE替换成SELECT *，确认目标记录是否正确：

   -- 验证用查询，检查要删除的记录
   SELECT *
   FROM Players_Tournaments pt
   JOIN Tournament t ON pt.Tournament_id = t.id
   JOIN Season s ON t.season_id = s.id
   WHERE s.year = 2020
   AND pt.Player_id IN (
       SELECT Player_id
       FROM Players_Tournaments pt_sub
       JOIN Tournament t_sub ON pt_sub.Tournament_id = t_sub.id
       JOIN Season s_sub ON t_sub.season_id = s_sub.id
       WHERE s_sub.year = 2020
       GROUP BY Player_id
       HAVING SUM(pt_sub.Points) = 0
   );
   

2. 备份数据：正式删除前建议备份Players_Tournaments表，防止误操作导致数据丢失

内容的提问来源于stack exchange，提问作者Luka Otočan