Лекция Генерация последовательностей (1)

1. Лекция Генерация последовательности. Рекурсивные СТЕ

2. 1. Объединение 100 запросов, выводящих по одному числу

SELECT 1 AS num
UNION ALL
SELECT 2
...
UNION ALL
SELECT 100;

3. 2. Способ использования декартова произведения двух таблиц

SELECT 10*(a-1)+b num
FROM
(SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) y;

4.

num
1
10
100
11
12
13
14
15
16
17
18
19
2
20
21
22
23
24
25
26
27
28
29
3
30
31
32
33
34
35
36
37
38
39
4
40
41
42
43
44
45
46
47
48
49
5
50
51
52
53
54
55
56
57
58
59
6
60
61
62
63
64
65
66
67
68
69
7
70
71
72
73
74
75
76
77
78
79
8
80
81
82
83
84
85
86
87
88
89
9
90
91
92
93
94
95
96
97
98
99

5. 3. Способ использования декартова произведения трех и более таблиц

SELECT 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
)z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1

6. 4. Способ использования рекурсивных СТЕ (MS SQL Server)

WITH <имя>[(<список столбцов>)]
WITH nums
AS (
AS (
< SELECT... > -- анкорная часть
SELECT 1 num
UNION ALL -- рекурсивная часть
UNION ALL
< SELECT...FROM <имя>… >
SELECT num+1 FROM nums
WHERE <условие продолжения итераций>
WHERE num+1<=100
)
)
Select * from <имя>
Select num from nums
По умолчанию количество итераций не больше 100!!!

7. 4. Способ использования рекурсивных СТЕ (PostgreSQL)

WITH RECURSIVE <имя>[(<список столбцов>)]
AS (
< SELECT... > -- анкорная часть
WITH RECURSIVE nums AS
(SELECT 1 num
UNION ALL
UNION ALL -- рекурсивная часть
SELECT num+1
< SELECT...FROM <имя>… >
FROM nums
WHERE <условие продолжения итераций>
WHERE num+1<=100)
)
Select * from <имя>
Select num from nums
По умолчанию количество итераций не больше 100!!!

8. Задача 1 Получить 100 последовательных незанятых номеров моделей, идущих за последним номером модели в таблице Product. ( Идея

для решения (1 способ):
находим максимальный номер модели и далее, используя генерацию последовательности, 100
последующих значений с шагом 1.
MS SQL Server
SELECT (SELECT MAX(model) FROM Product) + 5*5*(a-1)+5*(b-1) + c AS num
FROM
(SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1;

9. Задача 1 Получить 100 последовательных незанятых номеров моделей, идущих за последним номером модели в таблице Product. Идея

для решения (1 способ):
находим максимальный номер модели и далее, используя генерацию последовательности, 100
последующих значений с шагом 1.
PostgreSQL
SELECT (SELECT MAX(model:: integer) FROM Product) + 5*5*(a-1)+5*(b-1) + c AS num
FROM
(SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1;

10. Задача 1 Получить 100 последовательных незанятых номеров моделей, идущих за последним номером модели в таблице Product. Идея

для решения (2 способ):
Создаем СТЕ для нахождения максимального номера модели и далее, используем
рекурсивный СТЕ для 100 последующих значений.
MS SQL Server
With max_model as (SELECT cast (MAX(model) as int) +1 as model FROM Product),
num as (select model from max_model
Union all
Select model+1 from num
WHERE model+1<= 100+(select model from max_model)
)
Select model from num
Нельзя использовать агрегирующие функции, группировку в рекурсивной части СТЕ
«GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive
common table …»

11. Задача 1 Получить 100 последовательных незанятых номеров моделей, идущих за последним номером модели в таблице Product. Идея

для решения (2 способ):
Создаем СТЕ для нахождения максимального номера модели и далее, используем
рекурсивный СТЕ для 100 последующих значений.
PostgreSQL
With RECURSIVE num as
(SELECT MAX(model::integer)+1 as model FROM Product
Union all
Select model+1
from num
WHERE model+1<= 100 + (SELECT MAX(model::integer) as model FROM Product)
)
Select model from num

12. Задача 2 Вывести алфавитные символы - прописные латинские буквы

MS SQL Server
PostgreSQL
1 способ (с использованием декартова произведения
таблиц с числами)
1 способ (с использованием декартова произведения
таблиц с числами)
SELECT CHAR(ASCII('A')+5*(a-1) + b-1) AS letter
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT
3
UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
)y
WHERE 5*(a-1) + b <= 26
ORDER BY 1
SELECT CHR(ASCII('A')+5*(a-1) + b-1) AS letter
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL
SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL SELECT 6
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL
SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
)y
WHERE 5*(a-1) + b <= 26
ORDER BY 1

13.

letter
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

14. Задача 2 Вывести алфавитные символы - прописные латинские буквы

запросе анкорной части определяем ASCII-код
2 способ (с использованием рекурсивных СТЕ)Впервой
буквы алфавита и соответствующий ему
MS SQL Server
WITH Letters AS
(
SELECT ASCII('A') code, CHAR(ASCII('A')) letter
UNION ALL
SELECT code+1, CHAR(code+1) FROM Letters
WHERE code+1 <= ASCII('Z')
)
SELECT letter FROM Letters
символ.
В запросе рекурсивной части увеличивается
ASCII-код на единицу, обращаясь к CTE в
предложении FROM.
В результате к строке с первым символом будут
последовательно добавляться (UNION ALL)
строки со следующими буквами в порядке их
ASCII-кодов.
Итерации будут продолжаться до тех пор, пока
условие code +1 <= ascii('Z') будет истинным, т.е.
пока не будет добавлена буква "Z".
Оператор
SELECT letter FROM Letters
служит для обращения к CTE,
запуска рекурсии и вывода результата.

15. Задача 2 Вывести алфавитные символы - прописные латинские буквы

запросе анкорной части определяем ASCII-код
2 способ (с использованием рекурсивных СТЕ)Впервой
буквы алфавита и соответствующий ему
PostgreSQL
WITH RECURSIVE Letters AS
(
SELECT ASCII('A') code, CHR(ASCII('A')) letter
UNION ALL
SELECT code+1, CHR(code+1) FROM Letters
WHERE code+1 <= ASCII('Z')
)
SELECT letter FROM Letters
символ.
В запросе рекурсивной части увеличивается
ASCII-код на единицу, обращаясь к CTE в
предложении FROM.
В результате к строке с первым символом будут
последовательно добавляться (UNION ALL)
строки со следующими буквами в порядке их
ASCII-кодов.
Итерации будут продолжаться до тех пор, пока
условие code +1 <= ascii('Z') будет истинным, т.е.
пока не будет добавлена буква "Z".
Оператор
SELECT letter FROM Letters
служит для обращения к CTE,
запуска рекурсии и вывода результата.

16. Задача 3 Вывести последовательность дат между 26.02.2016 и 05.03.2016.

MS SQL Server
d
2016-02-26
WITH
dates as
(SELECT CONVERT (date,'20160226') d
UNION ALL
SELECT dateadd(dd,1,d) FROM dates
WHERE dateadd(dd,1,d) <= (SELECT CONVERT (date,'20160305') )
)
SELECT d FROM dates
2016-02-27
2016-02-28
2016-02-29
2016-03-01
2016-03-02
2016-03-03
2016-03-04
2016-03-05

17. Задача 3 Вывести последовательность дат между 26.02.2016 и 05.03.2016.

PostgreSQL
WITH RECURSIVE dates as
(SELECT '2016-02-26'::date as d
UNION ALL
SELECT d+1
FROM dates
WHERE d+1<= (SELECT '2016-03-05'::date))
d
2016-02-26
2016-02-27
2016-02-28
2016-02-29
2016-03-01
2016-03-02
2016-03-03
2016-03-04
SELECT d FROM dates
2016-03-05

18. Задача 4 Вывести последовательность дат между датами первого и последнего полета пассажира с id_psg=5.

d
MS SQL Server
WITH
2003-04-13 00:00:00.000
maxdate as
(SELECT max(date) max_date FROM pass_in_trip where id_psg = 5),
dates AS
(
SELECT MIN(date) d FROM pass_in_trip where id_psg = 5
UNION ALL
SELECT d+1 FROM dates
WHERE d+1 <= (SELECT max_date FROM maxdate)
)
2003-04-14 00:00:00.000
2003-04-15 00:00:00.000
2003-04-16 00:00:00.000
2003-04-17 00:00:00.000
2003-04-18 00:00:00.000
2003-04-19 00:00:00.000
2003-04-20 00:00:00.000
2003-04-21 00:00:00.000
2003-04-22 00:00:00.000
2003-04-23 00:00:00.000
2003-04-24 00:00:00.000
SELECT d FROM dates
2003-04-25 00:00:00.000
Нельзя использовать агрегирующие функции, группировку в рекурсивной части СТЕ
«GROUP BY, HAVING, or aggregate functions are not allowed in the recursive part of a recursive
common table …»

19. Задача 4 Вывести последовательность дат между датами первого и последнего полета пассажира с id_psg=5.

PostgreSQL
WITH RECURSIVE dates AS
(SELECT MIN(date::date) d FROM pass_in_trip where id_psg = 5
UNION ALL
SELECT d+1 FROM dates
WHERE d+1 <= (SELECT max(date::date)
FROM pass_in_trip
where id_psg = 5))
d
2003-04-13
2003-04-14
2003-04-15
2003-04-16
2003-04-17
2003-04-18
2003-04-19
2003-04-20
2003-04-21
2003-04-22
SELECT d FROM dates
2003-04-23
2003-04-24
2003-04-25

20.

Особые функции для PostgreSQL
Функция
generate_series(start, stop)
Тип аргумента
Тип результата
Описание
int, bigint
или numeric
setof int, setof
bigint или setof
numeric (определяется
типом аргумента)
Выдаёт ряд целых чисел
от start до stop с шагом 1
generate_series(start, stop, step) int, bigint
или numeric
setof int, setof
bigint или setof
numeric (определяется
типом аргумента)
Выдаёт ряд значений
от start до stop с
заданным шагом (step)
generate_series(start, stop, step i timestamp
setof timestamp или setof Выдаёт ряд значений
nterval)
или timestamp with timestamp with time
от start до stop с
time zone
zone (определяется типом заданным шагом (step)
аргумента)

21.

SELECT generate_series('2003-04-01'::timestamp,'2003-04-07'::timestamp, interval '1 day')
generate_series
------------------------------2003-04-01 00:00:00
2003-04-02 00:00:00
2003-04-03 00:00:00
2003-04-04 00:00:00
2003-04-05 00:00:00
2003-04-06 00:00:00
2003-04-07 00:00:00
SELECT generate_series('2003-04-01','2003-04-07', interval '1 day')::date
generate_series
------------------------------2003-04-01
2003-04-02
2003-04-03
2003-04-04
2003-04-05
2003-04-06
2003-04-07

22.

SELECT generate_series(1,50,5) AS N
SELECT generate_series(1,100) as N
N
------------------------------1
6
11
16
21
26
…..
N
------------------------------1
2
3
4
5
6
…..

23. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
Trip(trip_no, ID_comp, plane, town_from, town_to, time_out, time_in)
Pass_in_trip(trip_no, date, ID_psg, place)
Заменим задачу:
Для всех дней определить число рейсов из Rostov.
Вывод: дата, количество рейсов

24. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
Для всех дней определить число рейсов из Rostov.
Вывод: дата, количество рейсов
SELECT date, COUNT(DISTINCT trip_no) AS tripnum
FROM Pass_in_trip AS PiT
where PiT.trip_no IN (SELECT trip_no
FROM Trip
WHERE town_from='Rostov')
GROUP BY date

25. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
date
tripnum
2003-04-01 00:00:00.000
1
2003-04-05 00:00:00.000
1
2003-04-08 00:00:00.000
1
2003-04-13 00:00:00.000
1
2003-04-14 00:00:00.000
1
2003-04-29 00:00:00.000
1

26. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
Создадим дополнительную таблицу с датами между 01/04/2003 и
07/04/2003
• В запросе анкорной части СТЕ используйте преобразование типов
данных:
CONVERT (datetime,'20030401') tripdate
• В запросе рекурсивной части СТЕ используйте функцию
Dateadd (dd,1,tripdate) для увеличения даты на один день

27. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
Создадим дополнительную таблицу с датами между 01/04/2003 и
07/04/2003
WITH
Calend AS
(SELECT CONVERT(datetime,'20030401') AS tripdate
UNION ALL
SELECT dateadd (dd,1,tripdate)
FROM Calend
WHERE tripdate<'20030407')

28. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
К запросу
SELECT date, COUNT(DISTINCT trip_no) AS tripnum
FROM Pass_in_trip AS PiT
where PiT.trip_no IN (SELECT trip_no
FROM Trip
WHERE town_from='Rostov')
GROUP BY date
добавим левое соединение с таблицей CALEND по полю trip_no , а
дату будем выводить из таблицы CALEND.

29. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
SELECT Calend.tripdate, tripnum
FROM Calend LEFT JOIN (select date, COUNT(DISTINCT trip_no) AS tripnum
from Pass_in_trip AS PiT
where PiT.trip_no IN (SELECT trip_no
FROM Trip
WHERE town_from='Rostov')
group by date) y ON Calend.tripdate=y.date

30. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
tripdate
tripnum
2003-04-01 00:00:00.000
1
2003-04-02 00:00:00.000
NULL
2003-04-03 00:00:00.000
NULL
2003-04-04 00:00:00.000
NULL
2003-04-05 00:00:00.000
1
2003-04-06 00:00:00.000
NULL
2003-04-07 00:00:00.000
NULL

31. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
SELECT Calend.tripdate, coalesce(tripnum, 0)
FROM Calend LEFT JOIN (select date, COUNT(DISTINCT trip_no) AS tripnum
from Pass_in_trip AS PiT
where PiT.trip_no IN (SELECT trip_no
FROM Trip
WHERE town_from='Rostov')
group by date) y ON Calend.tripdate=y.date

32. Задача 66 Для всех дней в интервале с 01/04/2003 по 07/04/2003 определить число рейсов из Rostov. Вывод: дата, количество

рейсов
tripdate
tripnum
tripdate
tripnum
2003-04-01 00:00:00.000
1
2003-04-01 00:00:00.000
1
2003-04-02 00:00:00.000
NULL
2003-04-02 00:00:00.000
0
2003-04-03 00:00:00.000
NULL
2003-04-03 00:00:00.000
0
2003-04-04 00:00:00.000
NULL
2003-04-04 00:00:00.000
0
2003-04-05 00:00:00.000
1
2003-04-05 00:00:00.000
1
2003-04-06 00:00:00.000
NULL
2003-04-06 00:00:00.000
0
2003-04-07 00:00:00.000
NULL
2003-04-07 00:00:00.000
0

33.

MS SQL Server
PostgreSQL
WITH Calend AS
WITH RECURSIVE Calend AS
(SELECT CONVERT(datetime,'20030401') AS tripdate
(SELECT '20030401'::timestamp AS tripdate
UNION ALL
UNION ALL
SELECT dateadd (dd,1,tripdate)
SELECT tripdate + interval '1 day'
FROM Calend
FROM Calend
WHERE tripdate<'20030407')
WHERE tripdate<'20030407')
SELECT Calend.tripdate, coalesce(tripnum, 0) as tripnum
SELECT Calend.tripdate, coalesce(tripnum, 0) as tripnum
FROM Calend LEFT JOIN
FROM Calend LEFT JOIN
(select date, COUNT(DISTINCT trip_no) AS tripnum
(select date, COUNT(DISTINCT trip_no) AS tripnum
from Pass_in_trip AS PiT
from Pass_in_trip AS PiT
where PiT.trip_no IN (SELECT trip_no
where PiT.trip_no IN (SELECT trip_no
FROM Trip
FROM Trip
WHERE town_from='Rostov')
WHERE town_from='Rostov')
group by date) as y
group by date) as y
ON Calend.tripdate=y.date
ON Calend.tripdate=y.date

34.

PostgreSQL
PostgreSQL
WITH RECURSIVE Calend AS
(SELECT '20030401'::timestamp AS tripdate
UNION ALL
SELECT tripdate + interval '1 day'
FROM Calend
WHERE tripdate<'20030407')
SELECT Calend.tripdate, coalesce(tripnum, 0) as tripnum
SELECT Calend.tripdate, coalesce(tripnum, 0) as tripnum
FROM Calend
FROM (SELECT generate_series('2003-04-01'::timestamp,
'2003-04-07'::timestamp, interval '1 day') as tripdate) as Calend
LEFT JOIN
LEFT JOIN
(select date, COUNT(DISTINCT trip_no) AS tripnum
(select date, COUNT(DISTINCT trip_no) AS tripnum
from Pass_in_trip AS PiT
from Pass_in_trip AS PiT
where PiT.trip_no IN (SELECT trip_no
where PiT.trip_no IN (SELECT trip_no
FROM Trip
FROM Trip
WHERE town_from='Rostov')
WHERE town_from='Rostov')
group by date) as y
group by date) as y
ON Calend.tripdate=y.date
ON Calend.tripdate=y.date

35. Другой вариант (для обеих СУБД) без рекурсивного формирования последовательности дат

SELECT date, max(tripnum)
FROM (
SELECT date, COUNT(DISTINCT trip_no) AS tripnum
FROM Pass_in_trip
WHERE trip_no IN (SELECT trip_no
FROM Trip
WHERE town_from='Rostov')
and date>='2003-04-01' AND
date<='2003-04-07'
GROUP BY date
UNION ALL SELECT '2003-04-01',0
UNION ALL SELECT '2003-04-02',0
UNION ALL SELECT '2003-04-03',0
UNION ALL SELECT '2003-04-04',0
UNION ALL SELECT '2003-04-05',0
UNION ALL SELECT '2003-04-06',0
UNION ALL SELECT '2003-04-07',0
) AS t
ТОЛЬКО для PostgreSQL!!!!
SELECT date, max(tripnum)
FROM (
SELECT date, COUNT(DISTINCT trip_no) AS tripnum
FROM Pass_in_trip
WHERE trip_no IN (SELECT trip_no
FROM Trip
WHERE town_from='Rostov')
and date>='2003-04-01' AND
date<='2003-04-07'
GROUP BY date
UNION ALL
SELECT generate_series('2003-04-01'::timestamp,
'2003-04-07'::timestamp,
interval '1 day'),0
) AS t
GROUP BY date

36. PostgreSQL

CREATE SEQUENCE serial START 109;
SELECT nextval('serial')
Union
SELECT nextval('serial')
Union
SELECT nextval('serial')
Union
SELECT nextval('serial');

37. Задача №141

Для каждого из летавших пассажиров определить количество дней в апреле 2003
года, попавших в интервал между датами первого и последнего вылета пассажира
включительно.
Вывести имя пассажира и количество дней.
В ЛР 12 без генерации дат
Альтернативный способ в ЛР 14 с генерацией дат.

38. Разбор упражнения 9.20 (№151) (3 балла) из ЛР 4.

Найдите названия всех тех кораблей из базы данных, о которых
можно определенно сказать, что они были спущены на воду до 1941 г.

39.

SELECT name FROM Ships WHERE launched < 1941
UNION
SELECT name
FROM ships
WHERE name =any (SELECT class FROM Ships WHERE launched < 1941)
union
SELECT ship
FROM Outcomes
WHERE ship =any (SELECT class FROM Ships WHERE launched < 1941)

40.

UNION
SELECT ship
FROM Outcomes
where battle in (select name from battles WHERE date <'19410101')
UNION
SELECT name
FROM ships
WHERE name =any
(SELECT class FROM Ships where name =any
(select ship
from Outcomes
where battle in (select name from battles WHERE date <'19410101')
)
)
UNION
SELECT ship
FROM Outcomes
WHERE Ship =any
(SELECT class FROM Ships where name =any
(SELECT ship
FROM Outcomes
where battle in (select name from battles WHERE date <'19410101')))

41. Упражнение №121 (4 балла) из ЛР 11

Для каждого корабля из таблицы Ships указать название первого по времени сражения
из таблицы Battles, в котором корабль мог бы участвовать после спуска на воду.
Если год спуска на воду неизвестен, взять последнее по времени сражение.
Если нет сражения, произошедшего после спуска на воду корабля, вывести NULL вместо
названия сражения.
Считать, что корабль может участвовать во всех сражениях, которые произошли в год
спуска на воду корабля.
Вывод: имя корабля, год спуска на воду, название сражения
Замечание: считать, что не существует двух битв, произошедших в один и тот же день.

42.

MS SQL Server
WITH SIB AS (
SELECT Ships.name as s_name, launched, Battles.name as b_name, date
FROM Ships LEFT JOIN Battles ON launched<=DATEPART(yyyy, date)
OR launched IS NULL)
s_name
launched
b_name
date
California
1921
#Cuba62a
1962-10-20 00:00:00.000
California
1921
#Cuba62b
1962-10-25 00:00:00.000
California
1921
Guadalcanal
1942-11-15 00:00:00.000
California
1921
North Atlantic
1941-05-25 00:00:00.000
California
1921
North Cape
1943-12-26 00:00:00.000
California
1921
Surigao Strait
1944-10-25 00:00:00.000
New Jersey
1943
#Cuba62a
1962-10-20 00:00:00.000
New Jersey
1943
#Cuba62b
1962-10-25 00:00:00.000
New Jersey
1943
North Cape
1943-12-26 00:00:00.000
New Jersey
1943
Surigao Strait
1944-10-25 00:00:00.000
Название корабля1
………………..
Название корабля2
Null
Null
Null

43.

MS SQL Server
*) SELECT b_name
FROM SIB as SIB1
WHERE SIB1.s_name=Sib.s_name AND
date IN (SELECT MIN(date)
FROM SIB as SIB1
WHERE SIB1.s_name=SIB.s_name)
**) SELECT b_name
FROM SIB as SIB1
WHERE SIB1.s_name=Sib.s_name AND
date IN (SELECT MAX(date)
FROM SIB as SIB1
WHERE SIB1.s_name=SIB.s_name)
Коррелированные подзапросы, для
каждой строки основного запроса
формируется подзапрос, выбирающий
минимум/ максимум среди части строк
всей таблицы (для текущего корабля)

44.

MS SQL Server
WITH SIB AS (
SELECT Ships.name as s_name, launched, Battles.name as b_name,
date
FROM Ships LEFT JOIN Battles ON launched<=DATEPART(yyyy, date)
OR launched IS NULL)
Select distinct s_name, launched,
case
when launched<=DATEPART(yyyy, date)
then *
when launched IS NULL
then **
end as b_name
From SIB

45. Другое решение

MS SQL Server
Другое решение
with t as
(select name, launched,
(select case
when s.launched is null
then max(date)
else min(date)
end
from Battles
where datepart(year,date)> =coalesce(s.launched,0)
) as date
from Ships s)
select t.name, t.launched, b.name
from t left join Battles b on t.date=b.date