Объединение нескольких строк в одну строку с разными столбцами sql

у меня две таблицы

Таблица счетов

id   |   account_no  
-----------------------    
1    | 111 
2    | 222

Детали учетной записи

id   |   act_id (fk) | amount | created_dt_ | created_by
------------------------------------------------    
1    | 1             | 10       | 2022-10-30 | SYSTEM
2    | 1             | 100      | 2022-11-05 | user1
3    | 1             | 144      | 2022-11-10 | user2
4    | 1             | 156      | 2022-11-16 | user3
5    | 2             | 50      | 2022-11-05 | SYSTEM
6    | 2             | 51      | 2022-11-10 | user2
7    | 3             | 156      | 2022-11-16 | SYSTEM

Мне нужен запрос для извлечения только строк из сведений об учетной записи, которые имеют как минимум 2 записи для идентификатора учетной записи, и объединить эти строки в одну строку, демонстрирующую начальную сумму и пользователя, который ее создал, а также последнюю сумму и кто ее создал, что-то вроде это

act_id | ini_amt | ini_dt | ini_usr  | fnl_amt | fnl_dt | fnl_usr
-------------------------------------------------------------------------------------    
1      | 10   | 2022-10-30 | SYSTEM  | 156 | 2022-11-16 | user3
2      | 50   | 2022-11-05 | SYSTEM  | 51  | 2022-11-10 | user2

нам нужны только строки с более чем одной записью. Как мне это получить?

Какая у вас версия MySQL?

PM 77-1 18.11.2022 21:34
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
Доступ AWS Java Lambda к экземпляру AWS RDS MySQL с помощью CDK
Доступ AWS Java Lambda к экземпляру AWS RDS MySQL с помощью CDK
В этой статье мы рассмотрим, как включить доступ Java Lambda к экземпляру AWS RDS MySQL.
Как установить LAMP Stack 1/2 на Azure Linux VM
Как установить LAMP Stack 1/2 на Azure Linux VM
В дополнение к нашему предыдущему сообщению о намерении Azure прекратить поддержку Azure Database для MySQL в качестве единого сервера после 16...
1
1
191
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

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

В MySQL 8 вы можете сделать это так.

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

CREATE TABLE Account 
    (`id` int, `account_no` int)
;
    
INSERT INTO Account 
    (`id`, `account_no`)
VALUES
    (1, 111),
    (2, 222)
;

Records: 2  Duplicates: 0  Warnings: 0
CREATE TABLE Account_details
    (`id` int, `act_id` int, `amount` int, `created_dt_` varchar(10), `created_by` varchar(6))
;
    
INSERT INTO Account_details
    (`id`, `act_id`, `amount`, `created_dt_`, `created_by`)
VALUES
    (1, 1, 10, '2022-10-30', 'SYSTEM'),
    (2, 1, 100, '2022-11-05', 'user1'),
    (3, 1, 144, '2022-11-10', 'user2'),
    (4, 1, 156, '2022-11-16', 'user3'),
    (5, 2, 50, '2022-11-05', 'SYSTEM'),
    (6, 2, 51, '2022-11-10', 'user2'),
    (7, 3, 156, '2022-11-16', 'SYSTEM')
;
Records: 7  Duplicates: 0  Warnings: 0
WITH CTE_MIN as(
 SELECT
  `act_id`, `amount`, `created_dt_`, `created_by`,
  ROW_NUMBER() OVER(PARTITION BY `act_id` ORDER BY `created_dt_` ASC,`id` ASC) rn
  FROM Account_details),
   CTE_MAX as(
 SELECT
  `act_id`, `amount`, `created_dt_`, `created_by`,
  ROW_NUMBER() OVER(PARTITION BY `act_id` ORDER BY `created_dt_` DESC,`id` DESC) rn
  FROM Account_details)
SELECT
  mi.`act_id`, mi.`amount`, mi.`created_dt_`, mi.`created_by`, ma.`amount`, ma.`created_dt_`, ma.`created_by`
  FROM
CTE_MIN mi JOIN CTE_MAX ma 
  ON mi.`act_id` = ma.`act_id` 
  AND mi.rn = ma.rn  
  AND  mi.created_dt_!=ma.created_dt_
AND ma.rn = 1 ANd mi.rn = 1
act_id количество created_dt_ создан количество created_dt_ создан 1 10 2022-10-30 СИСТЕМА 156 2022-11-16 пользователь3 2 50 2022-11-05 СИСТЕМА 51 2022-11-10 пользователь2

рабочий пример

Добавьте AND mi.created_dt_!=ma.created_dt_, чтобы act_id 3 не попадало в набор результатов, так как у него только одна запись.

slaakso 18.11.2022 21:56

Может потребоваться ORDER BY по идентификатору таблицы сведений в дополнение к (или, возможно, вместо) created_dt_, чтобы обработать возможность более чем 1 строки сведений для одного и того же act_id в одну и ту же дату. Использование столбца даты и времени также справится с этим.

Paul Evans 19.11.2022 08:29

Это возможно, но необязательно, так как каждая учетная запись создается, но в случае нескольких записей мы могли бы отсортировать их также по I'd, когда я буду дома, я добавлю их в hnx.

nbk 19.11.2022 15:52

Спасибо @ nbk, это сработало как в исходной базе данных MySQL, так и в Snowflake, которая используется в качестве базы данных для отчетов.

Tathagata Roy 30.11.2022 15:35

В более старой версии MySQL, которая не поддерживает функции Windows:

select act_id,
       max(case when new_col='min_value' then amount     end) as ini_amt,
       max(case when new_col='min_value' then created_dt end) as ini_dt,
       max(case when new_col='min_value' then created_by end) as ini_usr,
       max(case when new_col='max_value' then amount     end) as fnl_amt,
       max(case when new_col='max_value' then created_dt end) as fnl_dt,
       max(case when new_col='max_value' then created_by end) as fnl_usr
 from (  

        select ad.id,ad.act_id,ad.amount,ad.created_dt,ad.created_by,'max_value' as new_col
        from AccountDetails ad
        inner join (select act_id,max(created_dt) as max_created_dt
                     from AccountDetails
                     group by act_id
                     having count(*) >=2
                   ) as max_val on max_val.act_id =ad.act_id and max_val.max_created_dt=ad.created_dt
    union 
        select ad1.id,ad1.act_id,ad1.amount,ad1.created_dt,ad1.created_by,'min_value'
        from AccountDetails ad1
        inner join (select act_id,min(created_dt) as min_created_dt
                     from AccountDetails
                     group by act_id
                     having count(*) >=2
                   ) as min_val on min_val.act_id =ad1.act_id and min_val.min_created_dt=ad1.created_dt
  ) as tbl
group by act_id;

https://dbfiddle.uk/q2Oxq0Ay

Мы можем сделать это без CTE, используя оконные функции и условную агрегацию:

select act_id,
    max(case when rn_asc  = 1 then amount     end)  ini_amount,
    max(case when rn_asc  = 1 then created_dt end)  ini_created_dt,
    max(case when rn_asc  = 1 then created_by end)  ini_created_by,
    max(case when rn_desc = 1 then amount     end)  fnl_amount,
    max(case when rn_desc = 1 then created_dt end)  fnl_created_dt,
    max(case when rn_desc = 1 then created_by end)  fnl_created_by
from(
    select ad.*,
        row_number() over(partition by act_id order by created_dt     ) rn_asc,
        row_number() over(partition by act_id order by created_dt desc) rn_desc,
        count(*)     over(partition by act_id) cnt 
    from account_details ad
) ad
where 1 in (rn_asc, rn_desc) and cnt > 1
group by act_id

В подзапросе row_number ранжирует записи одной и той же учетной записи по возрастанию и убыванию даты, а count проверяет количество записей в учетной записи.

Затем внешний запрос фильтрует учетные записи, которые имеют более одной записи, а также верхнюю/нижнюю запись. Затем мы можем повернуть набор данных с помощью group by и условных выражений, чтобы получить ожидаемый результат.

Другие вопросы по теме