国内最全IT社区平台 联系我们 | 收藏本站
华晨云阿里云优惠2
您当前位置:首页 > 数据库 > 数据库应用 > 将一张表中的数据作为列名的存储过程

将一张表中的数据作为列名的存储过程

来源:程序员人生   发布时间:2015-03-25 11:04:16 阅读次数:3114次

问题:在做项目的进程中遇到1个人问题:那就是将A表的deco字段的值,作为B表的列来展现,而且这些值的数据是从C表中取到的

方法:本来是想用,1个视图来列出deco字段里面的值和C表中的数据的,但是发现几张表的关联比较复杂(对应我来讲)

使用单纯的select语句,是不能得到想要的效果的,在网上找了相干资料后,发现可以用游标来对查询结果集中的每条

记录来处理,所以自己写出了以下的存储进程,这个存储进程会创建两张表,这两张表的简单关联就能够得到我想要的数据集。

代码:

/*创建进程*/ DELIMITER // DROP PROCEDURE IF EXISTS update_report // CREATE PROCEDURE update_report() BEGIN DECLARE done INT DEFAULT 0; DECLARE sql_alter VARCHAR(256) default ''; DECLARE sql_str VARCHAR(256) default ''; DECLARE a VARCHAR(200) DEFAULT ''; DECLARE b VARCHAR(200) DEFAULT ''; DECLARE c VARCHAR(200) DEFAULT ''; DECLARE kpi_value VARCHAR(200) DEFAULT ''; DECLARE mycursor CURSOR FOR SELECT id, name FROM dc_formula WHERE important=1; DECLARE projectcursor CURSOR FOR SELECT id from management_project; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; set @sql_alter='CREATE TABLE project_kpis ( id int(11) NOT NULL PRIMARY key AUTO_INCREMENT,project_id VARCHAR(20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8'; PREPARE sql_str from @sql_alter; EXECUTE sql_str; OPEN mycursor; REPEAT FETCH mycursor INTO a,b; IF NOT done THEN set b=REPLACE(b,'[','1'); set b=REPLACE(b,']','1'); set b=REPLACE(b,' ','_'); set b=REPLACE(b,'/','dv'); set @sql_alter=CONCAT('alter table project_kpis add ',b,' varchar(256)'); PREPARE sql_str from @sql_alter; EXECUTE sql_str; END IF; UNTIL done END REPEAT; set done=0; CLOSE mycursor; OPEN projectcursor; projectcursor:LOOP FETCH projectcursor INTO c; IF done = 1 THEN LEAVE projectcursor; END IF; insert into project_kpis(project_id) values(c); open mycursor; mycursor:LOOP FETCH mycursor INTO a,b; IF done = 1 THEN LEAVE mycursor; end IF; set b=REPLACE(b,'[','1'); set b=REPLACE(b,']','1'); set b=REPLACE(b,' ','_'); set b=REPLACE(b,'/','dv'); set @project_id=c; set @formula_id=a; select case when value is NULL then '' else value END val into @kpi_value from v_dc_projectreport WHERE project_id=@project_id and formula_id=@formula_id and language_range='All_exclude_jp'; SET @sql_alter = CONCAT('UPDATE project_kpis set ',b,'="',@kpi_value,'" where project_id=',@project_id); PREPARE sql_str from @sql_alter; EXECUTE sql_str; end LOOP mycursor; CLOSE mycursor; SET done=0; END LOOP projectcursor; CLOSE projectcursor; END // DELIMITER ; DELIMITER // DROP PROCEDURE if EXISTS kpi_report_pro // CREATE PROCEDURE kpi_report_pro() BEGIN DROP table if EXISTS project_info; create table project_info as SELECT * from (SELECT A.*, B.project_year PROJECT_YEAR FROM ( SELECT id PROJECT_ID, NAME PROJECT_NAME, version VERSION FROM management_project ) A LEFT JOIN dc_task B on A.PROJECT_ID=B.project_id GROUP BY A.PROJECT_ID) C; ALTER TABLE project_info ENGINE=MyISAM; DROP table if EXISTS project_kpis; call update_report(); select * from project_info t,project_kpis p where t.PROJECT_ID=p.project_id; END // DELIMITER ; /* 调用存储进程,存储进程调用后产生两张表,project_kpis和project_info 履行完后会输出报表结果*/ CALL kpi_report_pro()


生活不易,码农辛苦
如果您觉得本网站对您的学习有所帮助,可以手机扫描二维码进行捐赠
程序员人生
------分隔线----------------------------
分享到:
------分隔线----------------------------
关闭
程序员人生