mysql中如何计算同比环比_mysql 计算彩票中奖金额（存储过程的应用）

本案例是一个彩票数据案例，一张彩票有十个刮奖区，每个刮奖区由图符和奖符两部分组成，图符决定了奖金的翻倍倍数，奖符决定了奖金的基数。图符和奖符都由字母表示，具体对应关系见图片。那如果彩票工作人员需要对彩票数据做统计，统计每个人的中奖金额，提供的文件数据如下，在mysql中如何进行统计？

先理一下思路：

1、增加10列，用来存放拆分的十个图符字母

2、增加10列，用来存放拆分的十个奖符字母

3、为十个图符字母列赋值，将图符转换为倍数

4、为十个奖符字母列赋值，将奖符转换为奖金数

5、增加10列，用来存放每个刮奖区的倍数*奖金数

6、增加1列，对十个刮奖区的金额进行汇总求和

逻辑上是这样处理的，但是总共要增加30列是个比较庞大的数值了，而且语句之间有固定不变项，所以可以采用存储过程来处理；同样，需要为20列数据赋值，且语句之间有重复的，所以也采用存储过程来处理。

第1步：建表导数

-- 创建数据库
create database CP;  
use cp;
-- 创建彩票数据表
create table tableRaw(
	FNo varchar(10) not null,
    TNo varchar(10) not null,
    Mark varchar(20) not null,
    reward varchar(20) not null,
	bingovalue int not null
);
-- 导入彩票数据
load data local infile 'D:/zhouwork/final.csv' 
	into table tableRaw
    fields terminated by ','
    ignore 1 lines;
-- 增加自增字段
alter table tableraw add RowNumber int primary key auto_increment;
alter table tableraw modify RowNumber int first;

第2步：创建添加列的存储过程

-- 创建存储过程（指定删除或添加、个数、变量类型）
drop procedure ssp;
DELIMITER //
CREATE PROCEDURE ssp(in tablename varchar(30),in aod int, in cno int,in cname varchar(30),in cty varchar(100))
BEGIN
	declare i int default 1;
    declare si char(3) default '';
    set @final = concat('alter table ',tablename,' ');
    
    while i<cno do
		set si = cast(i as char(3));
		if aod = 1 then
			set @final = concat(@final,'add ',cname,si,' ', cty,',');
		else set @final = concat(@final,'drop ',cname,si,',');
        end if;
		set i = i + 1;
    end while;
    
    if aod = 1 then set @final = concat(@final,'add ',cname,cast(cno as char(3)),' ', cty,';');
    else set @final = concat(@final,'drop ',cname,cast(cno as char(3)),';');
    end if;
    
END //
DELIMITER ;
desc tableraw;

第3步：使用存储过程来添加30列

-- 使用存储过程（添加倍数、奖金、倍数value、奖金value）
call ssp('tableraw',1,10,'mark','varchar(1)');
call ssp('tableraw',1,10,'reward','varchar(1)');
call ssp('tableraw',1,10,'markvalue','varchar(1) not null default 0');
call ssp('tableraw',1,10,'rewardvalue','varchar(10) not null default 0');

select @final;
prepare a from @final;
execute a;

第4步:创建赋值的存储过程

-- 创建存储过程（为倍数和奖金金额赋值）
drop procedure sspp; 
DELIMITER //
CREATE PROCEDURE sspp(in tablename varchar(30),in cno int,in cname1 varchar(30),in cname2 varchar(30))
BEGIN
	declare i int default 1;
    set @final = concat('update ',tablename,' set ');
    
    while i < cno do
		set @final = concat(@final,cname1,i,'=substring(',cname2,',',i,',1),' );
		set i = i + 1;
	end while;
        set @final = concat(@final,cname1,cno,'=substring(',cname2,',',cno,',1);');
END //
DELIMITER ;

第5步：使用赋值的存储过程

-- 使用存储过程
set sql_safe_updates = 0;
call sspp('tableraw',10,'mark','mark');
call sspp('tableraw',10,'reward','reward');
select @final;
prepare a from @final;
execute a;

select * from tableraw;

第6步：建表导数

-- 创建图符规则表
drop table markmap;
create table markmap(
          mark char(1),
          markno int );
          
insert into  markmap values('m',1);
insert into  markmap values('n',2);
insert into  markmap values('o',2);
insert into  markmap values('p',2);

select * from markmap;

-- 创建奖符规则表
create table valuemap(
          mark char(1),
          markno int);

insert into valuemap values('A',5);
insert into valuemap values('B',10);
insert into valuemap values('C',20);
insert into valuemap values('D',25);
insert into valuemap values('E',50);
insert into valuemap values('F',100);
insert into valuemap values('G',200);
insert into valuemap values('H',500);
insert into valuemap values('I',1000);
insert into valuemap values('J',100000);

SELECT * FROM VALUEMAP;

第7步：表的连接

#注意：此处用内连接，用左连接会显示非空错误，因为左连接会调出空值，内连接不会调出空值

-- UPDATE markvalue  ---图符
update tableraw inner join markmap on markmap.mark = mark1 set markvalue1 = markno ;
update tableraw inner join markmap on markmap.mark = mark2 set markvalue2 = markno ;
update tableraw inner join markmap on markmap.mark = mark3 set markvalue3 = markno ;
update tableraw inner join markmap on markmap.mark = mark4 set markvalue4 = markno ;
update tableraw inner join markmap on markmap.mark = mark5 set markvalue5 = markno ;
update tableraw inner join markmap on markmap.mark = mark6 set markvalue6 = markno ;
update tableraw inner join markmap on markmap.mark = mark7 set markvalue7 = markno ;
update tableraw inner join markmap on markmap.mark = mark8 set markvalue8 = markno ;
update tableraw inner join markmap on markmap.mark = mark9 set markvalue9 = markno ;
update tableraw inner join markmap on markmap.mark = mark10 set markvalue10 = markno ;

-- update rewardvalue   --- 奖符
update tableraw inner join valuemap on valuemap.mark = reward1 set rewardvalue1 = markno;
update tableraw inner join valuemap on valuemap.mark = reward2 set rewardvalue2 = markno;
update tableraw inner join valuemap on valuemap.mark = reward3 set rewardvalue3 = markno;
update tableraw inner join valuemap on valuemap.mark = reward4 set rewardvalue4 = markno;
update tableraw inner join valuemap on valuemap.mark = reward5 set rewardvalue5 = markno;
update tableraw inner join valuemap on valuemap.mark = reward6 set rewardvalue6 = markno;
update tableraw inner join valuemap on valuemap.mark = reward7 set rewardvalue7 = markno;
update tableraw inner join valuemap on valuemap.mark = reward8 set rewardvalue8 = markno;
update tableraw inner join valuemap on valuemap.mark = reward9 set rewardvalue9 = markno;
update tableraw inner join valuemap on valuemap.mark = reward10 set rewardvalue10 = markno;

第8步：创建添加列和赋值的存储过程

-- 定义procedure，自动添加bingo1-10列
drop procedure sp;
delimiter //
create procedure sp(in cnoo int,in cno int, in cty varchar(30))
begin 
  declare i int default 1;
  declare si char(3) default '';
  set @final1 = 'alter table tableraw ';
  set @final2 = 'update tableraw set ';
while i < cno do
  set si = cast(i as char(5));
if cnoo = 1 then
  set @final1 = concat(@final1,'add bingo',si,' ',cty,',');
  set @final2 = concat(@final2,'bingo',si,'=markvalue',si,'*rewardvalue',si,',');
else set @final1 = concat(@final1,'drop bingo',si,' ,');
end if;
  set i = i+1;
end while;
if cnoo = 1 then
  set @final1 = concat(@final1,'add bingo',cast(cno as char(2)),' ',cty,';');
  set @final2 = concat(@final2,'bingo',cast(cno as char(2)),'=markvalue',cast(cno as char(2)),'*rewardvalue',cast(cno as char(2)),';');
else set @final1 = concat(@final1,'drop bingo',cast(cno as char(2)),' ;');
end if;
end //
delimiter ;

第9步：使用存储过程

-- 添加bingo1-10列
call sp(1,10,'int');
select @final1;
prepare a from @final1;
execute a;
-- 为bingo1-10列赋值，计算每个刮奖区的金额
set sql_safe_updates = 0;
select @final2;
prepare b from @final2;
execute b;

第10步：汇总

-- 汇总获奖金额
alter table tableraw add bingovalue1 int ;  #增加一列
update tableraw set bingovalue1 = bingo1+bingo2+bingo3+bingo4+bingo5+bingo6+bingo7+bingo8+bingo9+bingo10; #汇总
select sum(bingovalue),sum(bingovalue1) from tableraw; #验证