我正在尝试执行一个查询,在该查询中,我可以根据where子句获得列的总和,并按日期分组,但它的行为与我预期的不一样,也不确定原因。
我有一个交易表:
时间戳(transaction_date)
一个值(金额),
a类型(payin或支出),
交易前的前余额(结转),
以及期末余额(余额)。
以下是我的表的JSON导出:
[
{
"id":"2",
"type":"payout",
"carry":"2340",
"amount":"50",
"balance":"2290",
"transaction_date":"2014-09-15 00:00:00",
},
{
"id":"1",
"type":"payin",
"carry":"340",
"amount":"2000",
"balance":"2340",
"transaction_date":"2014-09-22 09:10:03",
},
{
"id":"3",
"type":"payout",
"carry":"0",
"amount":"50",
"balance":"0",
"transaction_date":"2014-09-22 09:10:03",
},
{
"id":"4",
"type":"payin",
"carry":"1",
"amount":"30",
"balance":"1",
"transaction_date":"2014-09-22 09:10:03",
}
]
我试图实现的是为每个日期(日历日期,而不是时间戳)获取一行,其中包含:
日期(同样是日历,而不是时间戳),
任何一天交易前的初始结转,
该日期的付款总额,
该日期的支出总额,
当天的最终期末余额。
目前我只得到一个结果,它似乎通过忽略了组(求和所有付款和所有付款,无论行日期如何)
SELECT DATE(`transaction_date`) as day,
SUM(amount) AS pay_in
FROM transactions
WHERE type='payin' GROUP BY day
这是我正在使用的完整查询(减去进位和余额)
SELECT payins.day, payins.pay_in, payouts.pay_out
From
(SELECT DATE(`transaction_date`) as day,
SUM(amount) AS pay_in FROM transactions
WHERE type='payin' GROUP BY DATE(`transaction_date`)
) payins
LEFT JOIN
(SELECT DATE(`transaction_date`) as day, SUM(amount) AS pay_out
FROM transactions
WHERE type='payout'
GROUP BY DATE(`transaction_date`)
) payouts
ON payins.day = payouts.day
Group By day
如果有什么不同的话,我在OSX 10.9 上使用MAMP 3.0.4
在这种情况下不需要使用子查询,可以将SUM
与CASE
一起使用。此外,你的分组似乎对我来说很好,为你的样本数据返回2个结果(见提供的fiddle):
SELECT Date(transaction_date) date,
SUM(CASE WHEN type='payin' THEN amount END)) payinsum,
SUM(CASE WHEN type='payout' THEN amount END) payoutsum
FROM transactions
GROUP BY date
- SQL Fiddle演示