mysql 统计sql 语句请教

程序员阿歪 2019-06-26 04:00:15
按日期统计总数
...全文
233 3 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
程序员阿歪 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)

56,876

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧