MySQL查询获得每月每天的付款总额，直到当前日期

Question

我试图建立一个MySQL查询，以获得每月每天的总金额，直到当前日期。我不确定仅凭一个查询是否可能。它应该是这样的:

今天的日期= '2021-02-24'

Select 
SUM(CASE WHEN transactionDate = '2020-02-01' THEN amount ELSE 0 END) AS Total01,
SUM(CASE WHEN transactionDate = '2020-02-02' THEN amount ELSE 0 END) AS Total03,
SUM(CASE WHEN transactionDate = '2020-02-03' THEN amount ELSE 0 END) AS Total03,
...
SUM(CASE WHEN transactionDate = '2020-02-24' THEN amount ELSE 0 END) AS Total24
From myTable

最后，我将得到每天的总amount。这有意义吗?

感谢

Answer 1

我宁愿这样写:

SELECT
sum(`amount`) as `amount`, DATE(`transactionDate`) as `date`
FROM
`myTable`
GROUP BY
DATE(`transactionDate`)
;

这将返回数据库中每个日期的行(仅转换为日期而不包含时间部分)