Вопрос:
Я застрял в упражнении 68 из http://www.sql-ex.ru/learn_exercises.php#answer_ref.
База данных состоит из этих четырех таблиц:
- Компания (ID_comp, имя)
- Поездка (trip_no, id_comp, plane, city_from, city_to, time_out, time_in)
- Пассажир (ID_psg, имя)
- Pass_in_trip (trip_no, date, ID_psg, place)
Целью упражнения является:
Узнайте количество маршрутов с наибольшим количеством рейсов (поездок).
Заметки.
- A – B и B – A следует рассматривать как САМЫЙ маршрут.
- Используйте только таблицу Trip.
Я могу получить правильный ответ “Узнайте количество маршрутов с наибольшим количеством полетов (поездок)”, но не при рассмотрении примечаний 1: A – B и B – A следует рассматривать как САМЫЙ маршрут.
Я не могу получить уникальные пары:
Если у нас есть выход:
| town_from | town_to | count | | ——— | ——— | —— | | London | Singapore | 4 | | Singapore | London | 4 |
Как выбрать, чтобы он дал мне
| town_from | town_to | count | | ——— | ——— | —— | | London | Singapore | 4 |
Мне удалось выполнить задачу со следующим запросом:
С x AS (
SELECT con, sum (c) как s FROM (
SELECT city_from, city_to, ‘con’ = CASE WHEN lower (city_from)
SELECT count (*) FROM x
WHERE s = (SELECT max (s) FROM x)
Лучший ответ:
Вам нужно посмотреть таблицу Trip таким образом, чтобы она представляла from- > так же, как и to- > from. Типичный способ сделать это – убедиться, что он всегда сортируется town_from <(=) town_to.
В общем случае следующий запрос будет проектировать Trip таким образом. Предложение case переключается с и вокруг, чтобы они всегда сортировались:
select trip_no, id_comp, plane, case when town_from < town_to then town_from else town_to end as town_from, case when town_from < town_to then town_to else town_from end as town_to, time_out, time_in from Trip
Затем вы можете выбрать из этого прогноза в своем запросе:
select … from ( select trip_no, id_comp, plane, case when town_from < town_to then town_from else town_to end as town_from, case when town_from < town_to then town_to else town_from end as town_to, time_out, time_in from Trip ) as x
Чтобы решить эту конкретную проблему, мы можем применить ту же логику, но удалить ненужные столбцы (оптимизатор должен делать это в любом случае, но он выглядит более чистым для человеческого глаза):
select town_from, town_to, count(*) from ( select case when town_from < town_to then town_from else town_to end as town_from, case when town_from < town_to then town_to else town_from end as town_to from Trip ) as x group by town_from, town_to
Примечание. Исправьте меня, если я ошибаюсь, но в вашем ожидаемом выпуске общее количество Лондона и Сингапура должно быть 8, а не 4. У вас есть 4 поездки в одном направлении и 4 в другом, из 8.
Затем мы можем использовать тот же запрос, чтобы найти наибольшее количество полетов, затем найти маршруты с этим числом, а затем подсчитать. Я подозреваю, что у вас есть эта часть, но это остается как упражнение.
