表格结构:
reference|transaction|member|summary|amount|action_by|created_at(unix timestamp)
我当前的Sql是:
select COALESCE(sum(`amount`) , 0) as `amount` , `transaction`
from `transactions`
where
`transaction` IN ('payment', 'deposit', 'withdraw') and
`created_at` >= unix_timestamp(DATE_SUB(NOW(), INTERVAL 15 DAY))
group by transaction
假设没有找到付款记录。我还是想看看结果。目前的结果是:
100 | deposit
200 | withdraw
我需要的结果是:
100 | deposit
200 | withdraw
0 | payment
我试了好几种方法,但都达不到预期的效果。请帮忙,我是sql的初学者。提前谢谢。
您需要有一个单独的表,每个表都有唯一的transaction
值。这可以用一个单独的表来完成,也可以使用下面的派生表:
SELECT COALESCE(sum(`amount`),0) as amount, der.transaction_type
FROM (SELECT 'payment' as transaction_type
UNION SELECT 'deposit'
UNION SELECT 'withdraw') der
LEFT JOIN `transactions` t on t.transaction=der.transaction_type AND `transaction` AND `created_at` >= unix_timestamp(DATE_SUB(NOW(), INTERVAL 15 DAY))
group by der.transaction_type
这会显示出来,但如果你的表中没有很多行,那么这应该不会太大。如果您在列