关于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或者触发器应该怎么做呢?
请高手能给出具体代码
[解决办法]
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');