CREATE DEFINER=`offsee`@`%` PROCEDURE `pro_terminal_report`
()
BEGIN
declare _version int ;
declare _currDate date ;
declare _tableName varchar(25);
declare _sql varchar(1000);declare cur_version cursor for select id from soft_version where calc_version = 1 order by ver asc;open cur_version;cur_version:loop
fetch cur_version into _version ;
set _sql = concat('select * from terminal_log_v',5);
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;end loop; set _currDate = date_format(now(),'%Y%m');insert into terminal_report(province , terminal_count , fee, terminalPayCount , arup , mnc , otime)
select province , count(1) as terminalCount , sum(fee)/100 as fee, count(distinct mobile) as terminalPayCount ,(sum(fee)/100)/count(distinct mobile) arup , mnc ,otime from
(
select province, id, fee , mobile , if(carrier=1,1,0) as mnc , date_format(order_time,'%Y%m') as otime from sync_order as so where date_format(order_time,'%Y%m') = _currDate union
select province, id, fee , mobile , if(carrier=1,1,0) as mnc , date_format(order_time,'%Y%m') as otime from sync_order_month as sm where date_format(order_time,'%Y%m') = _currDate
) t
where otime = _currDate
group by province order by fee desc;/****提交事务****/
END$$
()
BEGIN
declare _version int ;
declare _currDate date ;
declare _tableName varchar(25);
declare _sql varchar(1000);declare cur_version cursor for select id from soft_version where calc_version = 1 order by ver asc;open cur_version;cur_version:loop
fetch cur_version into _version ;
set _sql = concat('select * from terminal_log_v',5);
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;end loop; set _currDate = date_format(now(),'%Y%m');insert into terminal_report(province , terminal_count , fee, terminalPayCount , arup , mnc , otime)
select province , count(1) as terminalCount , sum(fee)/100 as fee, count(distinct mobile) as terminalPayCount ,(sum(fee)/100)/count(distinct mobile) arup , mnc ,otime from
(
select province, id, fee , mobile , if(carrier=1,1,0) as mnc , date_format(order_time,'%Y%m') as otime from sync_order as so where date_format(order_time,'%Y%m') = _currDate union
select province, id, fee , mobile , if(carrier=1,1,0) as mnc , date_format(order_time,'%Y%m') as otime from sync_order_month as sm where date_format(order_time,'%Y%m') = _currDate
) t
where otime = _currDate
group by province order by fee desc;/****提交事务****/
END$$
select * from terminal_log_v 5
你要达到什么目的
我要达到的目的是:
fetch cur_version into _version ;
set _sql = concat('select * from terminal_log_v',cur_version );
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;end loop; set _currDate = date_format(now(),'%Y%m');通过游标cur_version动态传递一个版本号cur_version, 然后根据这个版本号到对应的日志表terminal_log_v查询, 如这条语句_sql = concat('select * from terminal_log_v',cur_version );
.例如: 如果 cur_version 为5 , 那么我想执行的语句是: select * from terminal_log_v5,
如果 cur_version 为6 , 那么我想执行的语句是: select * from terminal_log_v6.
但是执行下面语句就报1064错误, 注释这段代码就正常.
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;
prepare _stmt from @_sql;
execute _stmt;
deallocate prepare _stmt;
fetch cur_version into _version ;set _sql = concat('select * from terminal_log_v',5 );这句写错了,应该是set _sql = concat('select * from terminal_log_v',_version );
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;end loop; set _currDate = date_format(now(),'%Y%m');通过游标cur_version动态传递一个版本号cur_version, 然后根据这个版本号到对应的日志表terminal_log_v查询, 如这条语句_sql = concat('select * from terminal_log_v',_version );
.例如: 如果 _version 为5 , 那么我想执行的语句是: select * from terminal_log_v5,
如果 _version 为6 , 那么我想执行的语句是: select * from terminal_log_v6.
但是执行下面语句就报1064错误, 注释这段代码就正常.
prepare _stmt from _sql;
execute _stmt;
deallocate prepare _stmt;
set @sql = concat('select * from terminal_log_v',cur_version );
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;