Оператор CASE, который выводит соседний столбец при совпадении (SQL)

Я пишу SQL-запрос и хочу создать оператор CASE, который выполняет следующие действия:

Для каждого Id я должен взять максимальный год из двух столбцов: End_Year и Complete_Year, и
Сопоставьте этот максимальный год с Country_Code, который соответствует ему из таблицы, либо из End_Year, либо из Complete_Year
Хотелось бы сделать это внутри оператора CASE из-за определенных технических ограничений. Это довольно сложно :/

Таблица выглядит так:

Идентификатор Код страны End_Year Complete_Year 101 США 2022 2023 101 АРГ 2021 2022 102 МОЖЕТ 2026 2025 102 МОЖЕТ 2022 2023

Результат, который я хотел бы, это:

Идентификатор Final_Country_Code Последний год 101 США 2023 102 МОЖЕТ 2026

В настоящее время мне удалось получить final_year, но не country_code, который соответствует максимальному end_year/complete_year для каждого идентификатора:

SELECT
id,
MAX(CASE WHEN (end_year >= complete_year) THEN end_year ELSE complete_year END) OVER (PARTITION BY id) AS final_year

FROM
sample_table

Хорошо, и что вы пробовали до сих пор

— 03.08.2023 07:06

Каков результат, если США и ARG имеют одинаковый максимальный год?

— 03.08.2023 07:13

@flyingfox Я проверил данные, таких случаев не было. Итак, по логике, тогда, может быть, первый (США)

— 03.08.2023 07:32

sql mysql case

03.08.2023 06:59

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вот несколько вариантов, как это сделать...

Давайте сначала скажем, что мы можем использовать GREATEST, чтобы получить большее из двух лет.

Затем мы можем, например, JOIN привязать таблицу к самой себе и проверить, что нет другой строки для того же идентификатора с большим годом.

Я прокомментировал шаги в запросе:

SELECT 
  main.id, main.country_code, 
  GREATEST(main.end_year, main.complete_year) AS finalYear # take the max of the two years
FROM yourtable main
  LEFT JOIN yourtable sub # self JOIN on the id
    ON main.id = sub.id 
    AND GREATEST(main.end_year, main.complete_year) < # compare the current year to other years of the same id
        GREATEST(sub.end_year, sub.complete_year)
  WHERE sub.id is NULL; # check there is no greater year for the current id

Еще один вариант получить желаемый результат — использовать CTE.

Сначала мы берем каждый идентификатор с его последним годом:

SELECT
  id, MAX(GREATEST(end_year, complete_year)) AS finalYear
FROM yourtable
  GROUP BY id;

Затем мы используем IN или JOIN, чтобы проверить, какие строки таблицы имеют правильный идентификатор и год.

Здесь с IN:

WITH maxData AS 
  (SELECT
    id, MAX(GREATEST(end_year, complete_year)) AS finalYear
  FROM yourtable
    GROUP BY id)
SELECT
  id, country_code, GREATEST(end_year, complete_year) AS finalYear
FROM yourtable
  WHERE (id, GREATEST(end_year, complete_year)) 
    IN (SELECT id, finalYear FROM maxData);

Здесь с JOIN:

WITH maxData AS 
  (SELECT
    id, MAX(GREATEST(end_year, complete_year)) AS finalYear
  FROM yourtable
    GROUP BY id)
SELECT
  y.id, y.country_code, GREATEST(y.end_year, y.complete_year) AS finalYear
FROM yourtable y 
INNER JOIN maxData d 
  ON y.id = d.id 
  AND GREATEST(y.end_year, y.complete_year) = d.finalYear;

Трудно сказать, какой подход является лучшим и действительно ли он имеет важное значение. Я думаю, что вариант с использованием CTE более удобочитаем, но это только мой «вкус».

Попробуйте выполнить приведенные выше запросы на этом примере скрипки

03.08.2023 08:08