案例:
Question:
如下的两个表,Table_A, Table_B
如果想通过A表的数据生成B 表的数据,根据A表的category为A 时,进B表的A_AMOUNT,为B时,进B_AMOUNT,为C时,进C_AMOUNT,
有无简单语句的写法可以完成,如一个语句就可完成的?
。(需要考滤执行效率)
Table_A
:
TransactionTypeCode PaymentTypeCode category AMOUNT
------------------- --------------- ---------- --------
1 1 A 60.00
1 1 B 80.00
1 1 C 30.00
1 2 B 40.00
1 2 C 30.00
2 1 A 40.00
2 1 B 30.00
2 1 C 40.00
Table_B:
TransactionTypeCode PaymentTypeCode A_AMOUNT B_AMOUNT C_AMOUNT
------------------- --------------- --------- ---------- --------
1 1 60.00 80.00 30.00
1 2 40.00 30.00
2 1 40.00 30.00 40.00
Answer:
SELECT
TransactionTypeCode, PaymentTypeCode,
A_AMOUNT = SUM(CASE category WHEN 'A' THEN AMOUNT ELSE 0 END),
B_AMOUNT = SUM(CASE category WHEN 'B' THEN AMOUNT ELSE 0 END),
C_AMOUNT = SUM(CASE category WHEN 'C' THEN AMOUNT ELSE 0 END)
FROM Table_A
GROUP BY TransactionTypeCode, PaymentTypeCode