Решение 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
листать
Консоль
