我有一个包含三列的事务表：transactionType，amount和transactionTimestamp。transactionType列接受1（现金），2（支票）或3（信用卡）的值。
我需要显示每天每个transactionType的总金额。我可以为每个transactionType使用单独的查询轻松地做到这一点：
SELECT SUM(amount) AS cashTotal, dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp)) AS transactionDay
FROM TransactionRecords
WHERE transactionType = 1
GROUP BY dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp));
SELECT SUM(amount) AS checkTotal, dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp)) AS transactionDay
FROM TransactionRecords
WHERE transactionType = 2
GROUP BY dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp));
SELECT SUM(amount) AS cardTotal, dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp)) AS transactionDay
FROM TransactionRecords
WHERE transactionType = 3
GROUP BY dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp));
但是我需要做的是在一个查询中进行。我尝试将上述内容合并为一个，如下所示：
SELECT
(SELECT SUM(amount) FROM TransactionRecords WHERE transactionType = 1 GROUP BY dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp))) AS cashTotal,
(SELECT SUM(amount) FROM TransactionRecords WHERE transactionType = 2 GROUP BY dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp))) AS checkTotal,
(SELECT SUM(amount) FROM TransactionRecords WHERE transactionType = 3 GROUP BY dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp))) AS cardTotal,
dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp)) AS transactionDay
FROM TransactionRecords
GROUP BY dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp));
但这只会导致“子查询返回的值大于1”的错误。而且我感觉这不是应该怎么做。感谢您的任何建议。
编辑：我应该澄清一点，我试图输出如下结果：
----------------------------------------------------
cashTotal | checkTotal | cardTotal | transactionDay
----------------------------------------------------
1000 | 1000 | 1000 | date
0 | 500 | 0 | date
----------------------------------------------------
每行显示该特定日期的每个transactionType的总金额。
编辑：编辑了一些详细信息以删除敏感信息。
只需对以下条件使用条件聚合CASE EXPRESSION：
SELECT
SUM(CASE WHEN paymentType = 1 THEN amount ELSE 0 END) as cashTotal,
SUM(CASE WHEN paymentType = 2 THEN amount ELSE 0 END) as chequeTotal,
SUM(CASE WHEN paymentType = 3 THEN amount ELSE 0 END) as cardTotal,
dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp)) AS transactionDay
FROM PaymentRecords
GROUP BY dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp));
虽然这一行看起来很奇怪
dateadd(DAY, 0, datediff(DAY, 0, transactionTimestamp))
您是要删节日期时间吗？如果是这样，请将其替换为：
CAST(transactionTimestamp as DATE)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句