56,678
社区成员
发帖
与我相关
我的任务
分享
mysql> select bno,date_format(dd,'%m月%d日') ddate,sum(type) 订购数,
-> count(*)-sum(type) 退订数
-> from orders
-> group by date_format(dd,'%m月%d日'),bno;
+------+----------+--------+--------+
| bno | ddate | 订购数 | 退订数 |
+------+----------+--------+--------+
| 111 | 09月01日 | 1 | 0 |
| 112 | 09月02日 | 0 | 1 |
| 111 | 09月03日 | 1 | 0 |
| 113 | 09月05日 | 0 | 1 |
+------+----------+--------+--------+
4 rows in set (0.00 sec)
mysql> select bno,date_format(dd,'%m月') ddate,sum(type) 订购数,
-> count(*)-sum(type) 退订数
-> from orders
-> group by date_format(dd,'%m月'),bno;
+------+-------+--------+--------+
| bno | ddate | 订购数 | 退订数 |
+------+-------+--------+--------+
| 111 | 09月 | 2 | 0 |
| 112 | 09月 | 0 | 1 |
| 113 | 09月 | 0 | 1 |
+------+-------+--------+--------+
3 rows in set (0.02 sec)
mysql> select date_format(dd,'%m月') ddate,sum(type) ord,
-> count(*)-sum(type) back
-> from orders
-> group by date_format(dd,'%m月');
+-------+------+------+
| ddate | ord | back |
+-------+------+------+
| 09月 | 2 | 2 |
+-------+------+------+
1 row in set (0.00 sec)
mysql> select * from orders;
+------+------+------+---------------------+
| id | bno | type | dd |
+------+------+------+---------------------+
| 1 | 111 | 1 | 2009-09-01 12:11:11 |
| 2 | 112 | 0 | 2009-09-02 12:11:11 |
| 3 | 111 | 1 | 2009-09-03 12:11:11 |
| 4 | 113 | 0 | 2009-09-05 12:11:11 |
+------+------+------+---------------------+
4 rows in set (0.00 sec)
mysql> select date_format(dd,'%m月%d日') ddate,sum(type) ord,
-> count(*)-sum(type) back
-> from orders
-> group by date_format(dd,'%m月%d日');
+----------+------+------+
| ddate | ord | back |
+----------+------+------+
| 09月01日 | 1 | 0 |
| 09月02日 | 0 | 1 |
| 09月03日 | 1 | 0 |
| 09月05日 | 0 | 1 |
+----------+------+------+
4 rows in set (0.00 sec)
日的:
select date(时间) as 统计时间,业务编号,sum(if(订购或退订=1,1,0)) as 订购数,sum(if(订购或退订=0,1,0)) as 退订数 from 表 group by date(时间),业务编号
月的:
select date_format(时间,'%Y-%m') as 统计时间,业务编号,sum(if(订购或退订=1,1,0)) as 订购数,sum(if(订购或退订=0,1,0)) as 退订数 from 表 group by date_format(时间,'%Y-%m'),业务编号