Решение 3.13.2
SELECT class, SUM ( sunk)
FROM ( SELECT class, COUNT ( *) AS sunk
FROM Ships a JOIN
Outcomes b ON a.name = b.ship AND
a.class <> b.ship
WHERE result = 'sunk'
GROUP BY class
UNION ALL
SELECT class, '1'
FROM Classes a JOIN
Outcomes b ON a.class = b.ship
WHERE result = 'sunk'
UNION ALL
SELECT class, '0'
FROM classes
) t
-- где классы с числом кораблей больше 2:
WHERE class IN ( SELECT t1.class
FROM ( SELECT a.class
FROM Classes a LEFT JOIN
Ships b ON a.class = b.class
) t1 LEFT JOIN ( SELECT DISTINCT ship
FROM Outcomes
WHERE ship NOT IN ( SELECT name
FROM Ships
)
) t2 ON t1.class = t2.ship
GROUP BY t1.class
HAVING COUNT ( *) > 2
)
GROUP BY class
HAVING SUM ( sunk) > 0 ;
Решение 3.13.3
SELECT a.class AS cls, a.num_sunks AS sunk
FROM ( SELECT c.class, COUNT ( o.ship) AS num_sunks
FROM Outcomes o LEFT JOIN
Ships s ON o.ship = s.name LEFT JOIN
Classes c ON s.class = c.class
WHERE o.result = 'sunk'
GROUP BY c.class) a,
( SELECT c1.class
FROM Ships s1, Classes c1
WHERE s1.class = c1.class
GROUP BY c1.class
HAVING COUNT ( name) >= 3
) B
WHERE a.class = b.class;
Решение 3.13.4
SELECT class, COUNT ( result) AS sunk
FROM ( SELECT class, result, name
FROM Ships LEFT JOIN
Outcomes ON ship=name AND
class IS NOT NULL AND
result = 'sunk'
) T
GROUP BY class
HAVING COUNT ( class) > 2 AND
COUNT ( result) > 0 ;
Проанализируйте тонкости вышеприведенных решений, самым красивым из которых, безусловно, является 3.13.4. Всего лишь одно соединение, для которого сразу подсчитывается как количество потопленных, так и общее число кораблей в классе. У этих решений имеется общая ошибка, о которой шла речь выше: не учтены головные корабли, которые присутствуют в таблице Outcomes и отсутствуют в таблице Ships.
ПиР
Решить задачу на SQL-EX.RU
листать
Консоль
