mysql 统计sql 语句请教

[程序员阿歪]

按日期统计总数

[遇星]

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)

• 打赏
• 举报

回复 赞

[AHUA1001]

借用楼上的建表内容。 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') ;

• 打赏
• 举报

回复 赞

[程序员阿歪]

--纸上得来终觉浅,绝知此事要躬行~~ 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;

• 打赏
• 举报

回复 赞