Mysql存储过程数据汇总（二）

最新推荐文章于 2024-10-13 21:08:42 发布

rsredwolf

最新推荐文章于 2024-10-13 21:08:42 发布

阅读量334

点赞数

分类专栏： MySQL 文章标签： mysql 存储 insert table excel

本文链接：https://blog.csdn.net/rsredwolf/article/details/7580733

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

下午又进行了整理，生成了临时表，并导成excel格式的，此格式需要完善

drop procedure if exists pro_main;
delimiter $$
create procedure pro_main(s varchar(6),e varchar(6),d varchar(6))
begin
declare rep_num varchar(30);
declare rep_id int;
declare rep_name varchar(20);
declare rep_efsnum decimal (10,2) default 0.00;
declare rep_sum decimal(10,2) default 0.00;
declare rep_count int default 0;
declare stop int default 0;

declare cur_main cursor for select id, housenumber,efsnum,ownername from detailstatement where FeeYearMonth between s and e and efsnum>0 and (housenumber like CONCAT('%区',d,'号%'));
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
open cur_main;
create temporary table if not exists cx (id int ,num varchar(50),su decimal(10,2),name varchar(20) );
fetch cur_main into rep_id, rep_num,rep_efsnum,rep_name;
while stop<>1 do
select rep_id,rep_num,rep_name,rep_efsnum;
set rep_sum=rep_sum+rep_efsnum;
set rep_count=rep_count+1;
insert into cx values(rep_id, rep_num,rep_efsnum,rep_name);
fetch cur_main into rep_id, rep_num,rep_efsnum,rep_name;
end while;
select rep_count as 合计,rep_sum as 用电量,CONCAT(d,'号楼') as 楼号;
insert into cx values(rep_count,CONCAT(d,'号楼'),rep_sum,'合计');
close cur_main;

end;

$$
delimiter ;

drop procedure if exists pro_rep1;
delimiter $$
create procedure pro_rep1(s varchar(6),e varchar(6))
begin
declare rep_count int default 0;
declare rep_num varchar(20);
declare rep_sum decimal(10,2) default 0.00;
declare stop int default 0;

declare cur_rep1 cursor for select DISTINCT substring_index(substring(housenumber,5),'号',1) from detailstatement where FeeYearMonth between s and e and efsnum>0 ;

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;
open cur_rep1;
drop temporary table if exists cx;
create temporary table if not exists cx (id int ,num varchar(50),su decimal(10,2),name varchar(20) );

fetch cur_rep1 into rep_num;
while stop<>1 do
call pro_main(s,e,rep_num);
fetch cur_rep1 into rep_num;
end while;

close cur_rep1;

select sum(su),count(id) into rep_sum,rep_count from cx where name not in('合计','总计') ;
insert into cx values(rep_count,'--河滨小区--',rep_sum,'总计');
end;
call pro_rep1('200910','200910');
$$
delimiter ;

select name,num,su,id from cx into outfile 'd:\\河滨小区2009年10月汇总表.xls';