Блог

Главная - Вопросы по программированию - Как получить столбцы из нескольких строк в одной строке в SQL

Как получить столбцы из нескольких строк в одной строке в SQL

#sql #sql-server #database #tsql

#sql #sql-server #База данных #tsql

Вопрос:

Я хочу получить значения 2 столбцов col_a и col_b для min и max какого-либо другого столбца. Например:

ID last_updated col_a col_b
1 2021-01-01 abc xyz
1 2021-01-02 abc_0 xyz_0
1 2021-01-03 abc_1 xyz_1
1 2021-01-04 abc_2 xyz_2
2 2021-01-01 abc xyz
2 2021-01-01 abc xyz
…

Я хочу получить результат:

 |1|abc|abc_2|xyz|xyz_2|

Это результат группировки по идентификатору и получения значений этих столбцов при установке условий min и max для какого-либо другого столбца (last_updated).

Я придумал следующий запрос:

 select id, max(last_updated), min(last_updated)
from my_table
group by id

Это дает мне идентификатор и минимальные и максимальные даты, но не другие 2 столбца. Я не уверен, как получить значения для других 2 столбцов для обеих дат в одном запросе.

Ответ №1:

Вы можете использовать MIN и MAX аналитическую функцию следующим образом:

 select id,
       max(case when mindt = last_updated then col_a end) as min_col_a,
       max(case when maxdt = last_updated then col_a end) as max_col_a,
       max(case when mindt = last_updated then col_b end) as min_col_b,
       max(case when maxdt = last_updated then col_b end) as max_col_b
  from
(select t.*,
       min(last_updated) over (partition by id) as mindt,
       max(last_updated) over (partition by id) as maxdt
  from your_table t) t
group by id

Ответ №2:

Мы можем использовать ROW_NUMBER дважды, чтобы найти первую и последнюю строки, упорядоченные по last_updated , для каждой id группы записей. Затем агрегируйте по id и выводите столбцы для различных col_a col_b значений и .

 WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY last_updated) rn_min,
              ROW_NUMBER() OVER (PARTITION BY id ORDER BY last_updated DESC) rn_max
    FROM yourTable
)

SELECT
    id,
    MAX(CASE WHEN rn_min = 1 THEN col_a END) AS col_a_min,
    MAX(CASE WHEN rn_max = 1 THEN col_a END) AS col_a_max,
    MAX(CASE WHEN rn_min = 1 THEN col_b END) AS col_b_min,
    MAX(CASE WHEN rn_max = 1 THEN col_b END) AS col_b_max
FROM cte
GROUP BY id;

Комментарии:

1. Как я могу получить их в отдельных столбцах? Я обновил вопрос

2. Ответ обновлен. Из-за вашего первоначального вопроса казалось, что вам нужна строка CSV.

Ответ №3:

Не самое простое решение, но демонстрирует другой способ получения нужных данных. Мы объединяем таблицу саму по себе, поскольку обычно нам нужны данные из 2 строк, затем мы используем cross apply , чтобы ограничить ее первой и последней.

 select T1.id, T2.col_a, T1.col_a, T2.col_b, T1.col_b
from #my_table T1
inner join #my_table T2 on T1.id = T2.id
cross apply (
  select id, max(last_updated) MaxLastUpdated, min(last_updated) MinLastUpdated
  from #my_table
  group by id
) X
where T1.last_updated = X.MaxLastUpdated and T2.last_updated = X.MinLastUpdated;

С предоставленными примерами данных это, по-видимому, работает хуже, чем row_number() решение. Самым быстрым решением являются аналитические функции.

Ответ №4:

 select id,max(last_updated) last_updatedMax, min(last_updated) last_updatedMin,max(col_aMax) col_aMax, max(col_aMin) col_aMin,max(col_bMax) col_bMax, max(col_bMin) col_bMin
from
(
    select 
        *
        , first_value(col_a) OVER (PARTITION BY  id ORDER BY last_updated desc) as  col_aMax
        , first_value(col_a) OVER (PARTITION BY  id ORDER BY last_updated asc) as  col_aMin
        , first_value(col_b) OVER (PARTITION BY  id ORDER BY last_updated desc) as  col_bMax
        , first_value(col_b) OVER (PARTITION BY  id ORDER BY last_updated asc) as  col_bMin
     from my_table
) t
group by id
Метки: