mysql 统计sql 语句请教

fallInLoveIT 2019-06-26 04:00:15
按日期统计总数
...全文
149 点赞 收藏 3
写回复
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
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)
回复
发帖
MySQL
创建于2007-09-28

5.5w+

社区成员

MySQL相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2019-06-26 04:00
社区公告
暂无公告