Сообщений в теме: 3

[Zufir]

Открыл тут для себя sql-ex.ru. Честно дошел до вопроса за нумером 75 и встал колом:
Схема базы: 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

Вроде бы учел все, но на проверочной базе выдает несоответствие данных.

Я понимаю, что это читерство и противоречит правилам sql-ex - но реально не могу понять, что тут не так

[Фшёшам]

Я бы в Постгресе так сделал:

CODE

select
s.name as "Название корабля",
s.launched as "Год спуска",
case when s.launched is null then (
-- сражение с максимальной датой
select
b.name
from Battles b
order by b."date" desc, b.name
limit 1
) else (
-- ближайшее сражение к году спуска
-- вернет NULL если нет сражения после года спуска
select
b.name
from Battles b
where
date_part('year', b."date")::integer >= s.launched
order by b."date" asc, b.name
limit 1
) end as "Название сражения"
from Ships s
order by 1

Если в Battles по "date" кластерный индекс, в принципе должно нормальную скорость давать даже на nested loop-ах. Сражение с максимальной датой можно зацепить на cross join, вероятно будет оптимальнее.
В транзакте аналог limit-а - top, если я правильно помню.

[Кутрапали]

Код

with shipslist (shipname, launched, battlename, [date]) as
(
select ships.name as 'shipname', launched, battles.name as 'battlename', battles.[date] from ships left join battles
on launched is null or launched <= datepart(year, [date])
),
battlelist (shipname, launched, battlename) as
(
select shipname, min(launched), (select top 1 battlename from shipslist l where outerlist.shipname = l.shipname order by [date] desc) from shipslist outerlist where launched IS NULL group by shipname
UNION ALL
select shipname, min(launched), (select top 1 battlename from shipslist l where outerlist.shipname = l.shipname order by ISNULL([date], '1800-01-01')) from shipslist outerlist where launched IS NOT NULL group by shipname
)
select * from battlelist

Чисто из спортивного интереса объяснять не буду

[Кутрапали]

Общий кстати совет по sql-ex, там обычно настолько долбанутые вопросы, что я стараюсь создавать пачками CTE, чтоб постепенно из имеющегося мусора строить нормальные данные. Все это можно конечно сделать в 1 здоровом запросе (чаще всего), но с ним так неудобно работать... Зато имея аккуратные CTE всегда можно в конце написать select * from cte и убедиться, что строящиеся там данные соответствуют твоим ожиданиям.