5.5w+
社区成员
...全文
请发表友善的回复…
发表回复
Java壹码平川 2019-08-28
--纸上得来终觉浅,绝知此事要躬行~~
select
DATE_FORMAT(add_date,'%Y-%m-%d') as datest,
sum(CASE WHEN side = 1 then amount else 0 end) as outAmount,
sum(CASE WHEN side = 2 then amount else 0 end) as inAmount
from table
group by datest;
AHUA1001 2019-06-27
借用楼上的建表内容。
mysql> select * from tab
-> ;
+--------+------+------------+---------------------+
| amount | side | history_id | add_date |
+--------+------+------------+---------------------+
| 10 | 1 | 1 | 2019-06-26 11:05:09 |
| 10 | 2 | 1 | 2019-06-26 16:30:13 |
| 10 | 1 | 1 | 2019-06-25 11:00:00 |
| 10 | 1 | 1 | 2019-06-26 11:05:11 |
| 10 | 1 | 2 | 2019-06-26 11:22:11 |
+--------+------+------------+---------------------+
5 rows in set (0.00 sec)
最后的结果。
select date_format(add_date,'%Y-%m-%d') `date`,sum(if(side=1,amount,0)) incountAmount,sum(if(side=2,amount,0) incountAmount from tab group by date_format(add_date,'%Y-%m-%d') ;
遇星 2019-06-26
mysql> select * from tab
-> ;
+--------+------+------------+---------------------+
| amount | side | history_id | add_date |
+--------+------+------------+---------------------+
| 10 | 1 | 1 | 2019-06-26 11:05:09 |
| 10 | 2 | 1 | 2019-06-26 16:30:13 |
| 10 | 1 | 1 | 2019-06-25 11:00:00 |
| 10 | 1 | 1 | 2019-06-26 11:05:11 |
| 10 | 1 | 2 | 2019-06-26 11:22:11 |
+--------+------+------------+---------------------+
5 rows in set (0.00 sec)
mysql> select a.`date`,sum(if(a.side=1,a.amount,0)) as incountAmount,sum(if(a.side=2,a.amount,0)) as extractAmount from (select date(add_date) as `date`,side,sum(amount) as amount from tab group by date(add_date),side) a group by a.`date`;
+------------+---------------+---------------+
| date | incountAmount | extractAmount |
+------------+---------------+---------------+
| 2019-06-25 | 10 | 0 |
| 2019-06-26 | 30 | 10 |
+------------+---------------+---------------+
2 rows in set (0.00 sec)