在SQL中多次连接同一张表

小编典典

在SQL中多次连接同一张表

sql

我有一个表，每个tran_ref列将有3行或更多行，并且每行将有数量以及tran_id跟随我的输入表

tran_ref | tran_id | amount
T1       |01.      | 9
T1       |02.      | -8
T1       |03       | -1
T2       |01.      | 5
T2       |02.      | -4
T2       |03       | -1
T2       |03       | -1

我想要其他表中的每个tran_ref一行

tran_ref |  amount_01 | amount_02| amount_03 | count_of_ref_rows
T1       | 9        | -8     | -1.     | 3
T2       | 5        | -4     | -2.     | 4

我尝试了加入，但没有按预期工作，谢谢:)

更新数据

如果这是桌子

tran_ref | tran_id | amount.        | currency    | Date
T1       |01.      | 9.              | USD.       | 02-09-2020
T1       |02.      | -8.             | INR.       | 03-09-2020
T1       |03       | -1.             | INR.       | 03-09-2020

需要更新的结果

 tran_ref |  amount_01 | amount_02| amount_03 | count | cur1| cur23|date1
 T1       | 9         | -8     | -1.     | 3.       | USD | INR |02-092020

请注意，currency1应该来自tran_id 1，currency23应该来自tranId23，日期应该仅来自tran_id 1

阅读 453

2021-04-15

共1个答案

小编典典

您可以Case表达式：

SELECT tran_ref, SUM(CASE WHEN tran_id = 1 THEN AMOUNT END) AS AMOUNT_01,
SUM(CASE WHEN tran_id = 2 THEN AMOUNT END) AS AMOUNT_02,
SUM(CASE WHEN tran_id = 3 THEN AMOUNT END) AS AMOUNT_03,
COUNT(*) count_of_ref_rows
FROM TABLE1
GROUP BY tran_ref;

另外，您可以为此使用PIVOT子句：

select a.*, b.count_total from
(SELECT tran_ref, [1] as amount_01, [2] as amount_02, [3] as amount_03
FROM TABLE1
PIVOT(sum(amount) FOR tran_id IN ([1] , [2], [3])) pivot_table) a
 inner join (SELECT tran_ref, count(1) count_total
FROM TABLE1 group by tran_ref) b on (a.tran_ref = b.tran_ref);

2021-04-15