表如下
id 业务编号 订购或退订 时间
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
统计出每天或者每月每项业务订购多少退订多少,想把数据导入到另一个新表中
如
时间 订购数 退订数
9月1日 111 55
时间 订购数 退订数
9月 111 55
10月 111 55
id 业务编号 订购或退订 时间
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
统计出每天或者每月每项业务订购多少退订多少,想把数据导入到另一个新表中
如
时间 订购数 退订数
9月1日 111 55
时间 订购数 退订数
9月 111 55
10月 111 55
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'),业务编号
,sum(if(订购或退订=0,1,0)) as 退订数
from tt group by DATE_FORMAT(时间,'%m-%d')select DATE_FORMAT(时间,'%m'),sum(if(订购或退订=1,1,0)) as 订购数 ,
,sum(if(订购或退订=0,1,0)) as 退订数
from tt group by DATE_FORMAT(时间,'%m')
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
--------------------------------------------
create table '订购' as select id,业务编号,订购,时间 from tb where 定购或退定=1;
create table '退定' as select id,业务编号,退定,时间 from tb where 定购或退定=0;
sum ... group by
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)
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)
,sum(if(订购或退订=0,1,0)) as 退订数
from tt group by 业务分类,DATE_FORMAT(时间,'%m-%d') select 业务分类,DATE_FORMAT(时间,'%m'),sum(if(订购或退订=1,1,0)) as 订购数 ,
,sum(if(订购或退订=0,1,0)) as 退订数
from tt group by 业务分类,DATE_FORMAT(时间,'%m')
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 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)