关于mysql触发器或SP的问题
原始数据表：
branch_id|flow_total|flow_free|flow_nofree|effect_time
1 | 256 | 128 | 128 | 2010-09-10 00:00:00
1 | 1024 | 256 | 768 | 2010-09-10 00:05:00
2 | 1024 | 256 | 768 | 2010-09-10 00:00:00
2 | 1024 | 256 | 768 | 2010-09-10 00:05:00
日表
branch_id|flow_total|flow_free|flow_nofree|effect_time
1 | 1280 | 384 | 896 | 2010-09-10
2 | 2048 | 512 | 1536 | 2010-09-10
周表
branch_id|flow_total|flow_free|flow_nofree|effect_time
1 | 1280 | 384 | 896 | 周日期
2 | 2048 | 512 | 1536 | 周日期
月表
branch_id|flow_total|flow_free|flow_nofree|effect_time
1 | 1280 | 384 | 896 | 2010-09-10
2 | 2048 | 512 | 1536 | 2010-09-10
年表
branch_id|flow_total|flow_free|flow_nofree|effect_time
1 | 1280 | 384 | 896 | 2010
2 | 2048 | 512 | 1536 | 2010
现在这个sp或者触发器应该怎么做呢？
请高手能给出具体代码
-
SQL code
create TABLE t10(
branch_id INT(11),
flow_total INT(11),
flow_free INT(11),
flow_nofree INT(11) ,
effect_time datetime);
insert into t10 values
(1,256,128,128,'2010-09-10 00:00:00'),
(1,1024,256,768,'2010-09-10 00:05:00'),
(2,1024,256,768,'2010-09-10 00:00:00'),
(2,1024,256,768,'2010-09-10 00:05:00');
select * from t10;
# 日
select branch_id,sum(flow_total),sum(flow_free),sum(flow_nofree),date_format(effect_time,'%Y-%m-%d')
from t10
group by branch_id,date_format(effect_time,'%Y-%m-%d')；
# 周
select branch_id,sum(flow_total),sum(flow_free),sum(flow_nofree),yearweek(effect_time)
from t10
group by branch_id,yearweek(effect_time)；
# 月
select branch_id,sum(flow_total),sum(flow_free),sum(flow_nofree),date_format(effect_time,'%Y-%m')
from t10
group by branch_id,date_format(effect_time,'%Y-%m')；
# 年
select branch_id,sum(flow_total),sum(flow_free),sum(flow_nofree),date_format(effect_time,'%Y')
from t10
group by branch_id,date_format(effect_time,'%Y')；
