mysql中的联合查询格式

Question

我正在MySQL训练。我有数据库叫船。我的目标是为每个等级定义=>，定义这类在战斗中沉没的船只的数量。结果集:等级，沉没船只的数量。以下是我想要得到结果的表格：

我试图用这样的方式解决我的问题：

SELECT classes.class, COUNT(outcomes.ship) as sunks
FROM classes LEFT JOIN outcomes ON classes.class=outcomes.ship
WHERE outcomes.result='sunk'
UNION
SELECT ships.class, COUNT(outcomes.ship) as sunks
                FROM ships
                LEFT JOIN outcomes ON ships.name=outcomes.ship
                WHERE outcomes.result='sunk'

我查询的结果集是: Bismark和Kogo类，但是其他类都没有了。

但这并不是我想要的。我想得到所有的类-即使是零outcomes.result。

我该怎么做才能让它正常工作？以下是表脚本，以防万一：船舶

Answer 1

第一个问题是，“通过使用引用该表的WHERE子句来覆盖左联接，所以结果中不能显示"NULL”行。

第二个问题是，您使用的是聚合(count())，但是您忘记了添加一个适当的组。

修复这些查询如下所示：

SELECT class, count(ship) as sunks
FROM (
  ( 
   SELECT classes.class, outcomes.ship
   FROM classes
   LEFT JOIN outcomes ON classes.class=outcomes.ship AND outcomes.result='sunk'
  )
  UNION
  (
   SELECT ships.class, outcomes.ship
   FROM ships
   LEFT JOIN outcomes ON ships.name=outcomes.ship AND outcomes.result='sunk'
  )
) AS tmp
GROUP BY class

有一个小提琴是根据你的脚本看到它在行动的http://sqlfiddle.com/#!9/4840f6/8

Answer 2

我认为最好的方法是：

select
c.class,
(select count(o.ship) from outcomes o
left join ships s on s.name = o.ship
where (c.class = o.ship or c.class = s.class)
and o.result = 'sunk') as sunks
from
classes c

这样，如果船没有沉没，我将得到所有有沉没表列和零结果的类。