Блог

Главная - Вопросы по программированию - Создайте временную таблицу из двух таблиц, выбрав последнюю дату перед определенной датой

Создайте временную таблицу из двух таблиц, выбрав последнюю дату перед определенной датой

#mysql #sql #datetime #subquery #greatest-n-per-group

#mysql #sql #datetime #подзапрос #наибольшее число на группу

Вопрос:

У меня есть следующие две таблицы

FlightTable

 | passenger_id | flight_date       | flight_number  | destination  | 
| -------------| ----------        | -------------- | ------------ |
| 1234         | 2020-12-16        | 534            | CA           |
| 1234         | 2020-12-29        | 876            | FL           |
| 1234         | 2020-11-13        | 938            | FL           |
| 5678         | 2020-12-27        | 986            | MN           |
| 5678         | 2020-11-19        | 347            | WA           |

Таблица пассажиров

 | passenger_id | company_name | phone_number   | 
| -------------| ------------ | ------------   |
| 1234         | Verizon      | (555) 874-9232 |
| 5678         | ATamp;T         | (555) 867-5309 |

Я хочу создать временную таблицу, которая включает определенные столбцы из обеих таблиц, но включает только строку из FlightTable, которая имеет последнюю дату flight_date, происходящую до 2020-12-28 для каждого пассажира. Результирующая таблица должна выглядеть следующим образом:

TempTable

 | passenger_id | company_name |flight_number  | destination  | 
| -------------| ------------ |------------   | ------------ |         
| 1234         | Verizon      |534            | CA           |
| 5678         | ATamp;T         |986            | MN           |

Я безуспешно пытался выполнить следующий запрос:

 CREATE TEMPORARY TABLE TempTable AS (
SELECT F.passenger_id, P.company_name, flight_number, destination
FROM FlightTable AS F, PassengerTable AS P
WHERE '2020-12-28' <= MAX(flight_date) AND F.passenger_id = P.passenger_id  
);

Как это можно изменить, чтобы работать должным образом?

Ответ №1:

Во-первых, научитесь использовать правильный JOIN синтаксис. Затем вы можете использовать коррелированный подзапрос, чтобы выбрать самую последнюю дату перед вашим отсечением:

 SELECT F.passenger_id, P.company_name, f.flight_number, f.destination
FROM FlightTable F JOIN
     PassengerTable P
     ON F.passenger_id = P.passenger_id  
WHERE f.flight_date = (SELECT MAX(f2.flight_date) 
                       FROM FlightTable f2
                       WHERE f2.passenger_id = f.passenger_id AND
                             f2.flight_date < '2012-12-28'
                      );

Ответ №2:

Один из вариантов использует подзапрос:

 select p.passenger_id, p.company_name, f.flight_number, f.destination
from passenger p
inner join flight f on f.passenger_id = p.passenger_id
where f.flight_date = (
    select max(f1.flight_date)
    from flight f1
    where f1.passenger_id = f.passenger_id and f1.flight_date < '2020-12-28'
)

В качестве альтернативы вы можете использовать оконные функции:

 select p.passenger_id, p.company_name, f.flight_number, f.destination
from passenger p
inner join (
    select f.*,
        rank() over(partition by passenger_id order by flight_date desc) rn
    from flight f
    where flight_date < '2020-12-28'
) f on f.passenger_id = p.passenger_id
where rn = 1