Схема базы: http://sql-ex.ru/hel...lect13.php#db_3
Рассматривается БД кораблей, участвовавших во второй мировой войне. Имеются следующие отношения:
Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)
Корабли в «классах» построены по одному и тому же проекту, и классу присваивается либо имя первого корабля, построенного по данному проекту, либо названию класса дается имя проекта, которое не совпадает ни с одним из кораблей в БД. Корабль, давший название классу, называется головным.
Отношение Classes содержит имя класса, тип (bb для боевого (линейного) корабля или bc для боевого крейсера), страну, в которой построен корабль, число главных орудий, калибр орудий (диаметр ствола орудия в дюймах) и водоизмещение ( вес в тоннах). В отношении Ships записаны название корабля, имя его класса и год спуска на воду. В отношение Battles включены название и дата битвы, в которой участвовали корабли, а в отношении Outcomes – результат участия данного корабля в битве (потоплен-sunk, поврежден - damaged или невредим - OK).
Замечания. 1) В отношение Outcomes могут входить корабли, отсутствующие в отношении Ships. 2) Потопленный корабль в последующих битвах участия не принимает.
Задачка:
Для каждого корабля из таблицы Ships указать название ближайшего по времени сражения из таблицы Battles, в котором корабль мог бы участвовать после спуска на воду. Если год спуска на воду неизвестен, взять последнее по времени сражение.
Если нет сражения, произошедшего после спуска на воду корабля, вывести NULL вместо названия сражения.
Замечание. Считать, что корабль может участвовать в сражении, которое произошло в год спуска на воду корабля.
Вывод: имя корабля, год спуска на воду, название сражения
Что смог родить:
Код
with shp as (select ships.name, ships.launched, case when ships.launched is null then (select max(year(date)) from battles) else ships.launched end as lyear from ships),
shpbt as (select ships.name, ships.launched as launched, battles.name as battle, battles.date from shp ships
left join battles on (year(battles.date)>=ships.lyear))
select name, launched, battle from shpbt where date=(select min(date) from shpbt ss where ss.name=shpbt.name) or battle is null
shpbt as (select ships.name, ships.launched as launched, battles.name as battle, battles.date from shp ships
left join battles on (year(battles.date)>=ships.lyear))
select name, launched, battle from shpbt where date=(select min(date) from shpbt ss where ss.name=shpbt.name) or battle is null
Вроде бы учел все, но на проверочной базе выдает несоответствие данных.
Я понимаю, что это читерство и противоречит правилам sql-ex - но реально не могу понять, что тут не так