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

最新推荐文章于 2023-11-16 11:26:32 发布

rsredwolf

最新推荐文章于 2023-11-16 11:26:32 发布

阅读量347

点赞数

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

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

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

今天花了一上午把查询搞出来了，还没有完善好，先贴出来

drop procedure if exists pro_rep;
delimiter $$

create PROCEDURE pro_rep(s varchar(6),e varchar(6))
begin

declare rep_id int;
declare rep_jnumber varchar(20);
declare rep_rcname varchar(30);
declare rep_ownername varchar(10);
declare rep_efsnum varchar(20);
declare rep_month varchar(6);
declare rep_count int default 0;
declare rep_sum decimal (10,2) default 0.00;

declare stop int default 0;

declare cur_rep cursor for select id,JournalNumber,housenumber,OwnerName,efsnum,FeeYearMonth
from detailstatement where FeeYearMonth between s and e and efsnum>0;

declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

open cur_rep;

fetch cur_rep into rep_id,rep_jnumber,rep_rcname,rep_ownername,rep_efsnum,rep_month;

while stop<>1 do

select rep_id as 编号,rep_jnumber,rep_rcname,rep_ownername,rep_efsnum,rep_month;
set rep_sum=rep_sum+rep_efsnum;
set rep_count=rep_count+1;
fetch cur_rep into rep_id,rep_jnumber,rep_rcname,rep_ownername,rep_efsnum,rep_month;
end while;
select rep_count as 编号,rep_sum as 用电量;
close cur_rep;
end;
call pro_rep('200910','200910');
$$
delimiter ;

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;
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;
fetch cur_main into rep_id, rep_num,rep_efsnum,rep_name;
end while;
select rep_count as 合计,rep_sum as 用电量,CONCAT(d,'号楼') as 楼号;
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_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;
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;
end;
call pro_rep1('200910','200910');
$$
delimiter ;

select id, housenumber, from detailstatement where FeeYearMonth between '200910' and '200910' and efsnum>0 and (housenumber like '%区二号%' );
select DISTINCT substring_index(substring(housenumber,5),'号',1) as number from detailstatement where feeyearm ;
select id from detailstatement where housenumber like '%区二十六号%' and feeyearm;