在 SQL 中多次连接同一个表[重复]

我有一个表，每个 tran_ref 列有 3 行或更多行，每行都有 amount 以及 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

您可以使用 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);