根据付款模式对数据进行分组时发现问题

Question

我有下面描述的表结构。

表1:

   ID   Name
   1    Sar
   2    Varun
   3    Sagar

表2:

   Pay Id   ID  Payment Mode    Amount
   1    1   Cash                 500
   2    1   Credit Card          1000
   3    1   Debit Card           10000
   4    1   Net Banking          5000
   5    1   Gift Voucher         100

需要获得以下指定的结果：

   ID   Cash    Credit Card Debit Card  Others
   1    500        1000       10000      5100

你能帮我吗。

Answer 1

如果使用Oracle 11g ，请使用pivot

WITH table_1(PayId,ID,PaymentMode, Amount) AS 
(SELECT 1, 1, 'Cash', 500 FROM dual UNION ALL
 SELECT 2, 1, 'Credit Card', 1000 FROM dual UNION ALL
 SELECT 3, 1, 'Debit Card', 10000 FROM dual UNION ALL
 SELECT 4, 1, 'Net Banking', 5000 FROM dual UNION ALL
 SELECT 5, 1, 'Gift Voucher', 100 FROM dual),
table_2(ID, Name) AS
(SELECT 1, 'Sar' from dual UNION ALL
 SELECT 2, 'Varun' from dual UNION ALL
 SELECT 3, 'Sagar' from dual),
------------------------------
--- Preparation of data ends
------------------------------
table_final AS 
(select t1.ID, t1.amount, CASE t1.paymentmode 
                       WHEN 'Cash' THEN 'Cash'
                       WHEN 'Credit Card' THEN 'Credit Card'
                       WHEN 'Debit Card' THEN 'Debit Card'
                       ELSE 'Others'
                   END AS payemnt_mod
  FROM table_1 t1 
  JOIN table_2 t2 
    ON (t1.id = t2.id))
SELECT * 
  FROM table_final
PIVOT (SUM(amount) AS total FOR payemnt_mod IN ('Cash' AS cash, 'Credit Card' AS credit_card, 'Debit Card' AS debit_card, 'Others' AS OTHERS));

输出：

ID  CASH_TOTAL  CREDIT_CARD_TOTAL   DEBIT_CARD_TOTAL    OTHERS_TOTAL
1   500         1000                10000               5100

您的查询将是

with table_final AS 
    (select t1.ID, t1.amount, CASE t1.paymentmode 
                           WHEN 'Cash' THEN 'Cash'
                           WHEN 'Credit Card' THEN 'Credit Card'
                           WHEN 'Debit Card' THEN 'Debit Card'
                           ELSE 'Others'
                       END AS payemnt_mod
      FROM table_1 t1 
      JOIN table_2 t2 
        ON (t1.id = t2.id))
    SELECT * 
      FROM table_final
    PIVOT (SUM(amount) AS total FOR payemnt_mod IN ('Cash' AS cash, 'Credit Card' AS credit_card, 'Debit Card' AS debit_card, 'Others' AS OTHERS));