将多行合并为具有不同列的一行( sql )

Question

我有两张桌子

帐号表

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

我需要一个查询，只从帐户详细信息(至少有两个帐户id记录)中获取行，并将这些行合并为一行，显示创建该帐户的初始金额和用户以及创建该数据的用户，如下所示

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

我们只需要有多个记录的行。我该怎么拿呢？

Answer 1

在不支持windows函数的旧MySQL版本上：

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

Answer 2

我们可以在不使用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和条件表达式对数据集进行枢轴，以产生预期的结果。