使用 Group by 查询指定结果的 MySQL 查询

Question

我需要编写一个查询，在按查询分组后返回一行。例如，我有一个表 tblTest，其中包含以下列:

Id, Name,   Type,  Amount
1, Receipt, Cash   100
2, Receipt, Card   200
3, Receipt, Cheque 250
4, Receipt, Card   150
5, Receipt, Cash   100
6, Payment, Cash   300
7, Payment, Cheque 400

分组查询后

Select Name,Type,Amount from tblTest where 1=1 Group by Type

它返回我，

1 Receipt  Cash   200
2 Receipt  Card   350
3 Receipt  Cheque 250
4 Payment, Cash   300
5 Payment, Cheque 400

但我需要返回的是类似

Name    Cash Card Cheque
Receipt 200  350  250
Payment 300  NULL 400

提前致谢..

Answer 1

您只需要一个基本的数据透视查询和一个求和:

SELECT
    Name,
    SUM(CASE WHEN Type = 'Cash'   THEN Amount ELSE 0 END) Cash,
    SUM(CASE WHEN Type = 'Card'   THEN Amount ELSE 0 END) Card,
    SUM(CASE WHEN Type = 'Cheque' THEN Amount ELSE 0 END) Cheque
FROM tblTest
GROUP BY
    Name;

Demo