二战海军舰船数据库SQL查询调试：结果少一条记录求助

看起来你的SQL在统计范围上有遗漏，导致测试数据库少了一条记录。咱们来拆解一下问题所在：

核心问题点 #

你的当前查询只考虑了Ships表中存在的舰船，但题目明确说明：仅在Outcomes表中的舰船也视为数据库内的舰船，即使已被击沉。这意味着你漏掉了两种关键情况：

• 某个级别有部分舰船只出现在Outcomes中，导致该级别总舰船数实际≥3，但你的子查询只统计Ships里的数量，没把这些算进去；
• 部分被击沉的舰船只在Outcomes中存在，你的主查询没统计这些击沉记录。

另外还有几个小问题：

• WHERE result='sunk'会把LEFT JOIN变成等价的INNER JOIN，过滤掉没有击沉记录的条目，但其实我们应该保留所有级别，再统计其中的击沉数；
• HAVING count(result) is not NULL是多余的，因为COUNT(result)对于没有击沉记录的组会返回0，而非NULL；
• SELECT DISTINCT在主查询里没必要，因为GROUP BY c.class已经保证了每个级别只出现一次。

修正后的SQL #

我们需要先构建一个包含所有舰船（Ships + Outcomes中不在Ships里的）及其所属级别的数据集，再基于这个数据集统计级别总舰船数和击沉数：

WITH AllShips AS (
    -- 整合所有舰船及其级别：Ships里的直接取class，Outcomes里独有的船默认级别是自身名称（符合级别名通常为首舰名的规则）
    SELECT name AS ship, class
    FROM Ships
    UNION
    SELECT ship, ship AS class
    FROM Outcomes
    WHERE ship NOT IN (SELECT name FROM Ships)
)
SELECT 
    c.class,
    COUNT(DISTINCT CASE WHEN o.result = 'sunk' THEN o.ship END) AS sunk_ships
FROM Classes c
-- 关联所有属于该级别的舰船
JOIN AllShips s ON c.class = s.class
-- 左关联战役结果，保留所有舰船记录
LEFT JOIN Outcomes o ON s.ship = o.ship
GROUP BY c.class
-- 筛选条件：级别总舰船数≥3，且至少有1艘被击沉
HAVING 
    COUNT(DISTINCT s.ship) >= 3
    AND COUNT(DISTINCT CASE WHEN o.result = 'sunk' THEN o.ship END) > 0

为什么这样能解决问题？ #

1. AllShips CTE：完整覆盖了数据库中所有舰船，包括仅在Outcomes里的，确保级别总舰船数统计准确；
2. COUNT(DISTINCT s.ship)：避免重复统计同一艘船（比如一艘船可能在Outcomes里有多个战役记录）；
3. CASE WHEN统计击沉数：精准计算该级别被击沉的舰船数量，同时不影响总舰船数的统计；
4. HAVING子句：同时满足“总舰船数≥3”和“有击沉记录”两个核心条件，完全匹配题目要求。

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