mysql 动态行转列问题

[gg_win]

代码跟结果如一二图所示，第三图是未转行之前，为什么负值没有成功执行呢？求大神帮忙解答

[gg_win]

就是goal只有大于或等于0的，负值都用0表示了

[中国风]

参照例子： e.g.

drop table if exists  Class;
Create table Class(Student nvarchar(2),Course nvarchar(2),Score int);

Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85 ;

#方法1
set @sql='';  
select group_concat('max(case when Course=''',Course,''' then Score else 0 end) as ',Course) INTO @sql from (select distinct Course from  class ) as a;
set @sql=concat('select Student,',@sql,' from Class group by Student') ; 
prepare stmt from @sql;  
execute stmt ;  
DEALLOCATE PREPARE stmt;

[gg_win]

楼上说的就是我用的方法，可看图一，行转列功能已经实现，正分值是会成功，可是负分值没有转行成功，给了一个0值

[gg_win]

希望大神帮忙啊，就是根据网上mysql行转列例子来的

[ACMAIN_CHM]

引用

建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。 参考一下这个贴子的提问方式http://bbs.csdn.net/topics/320211382 1. 你的 create table xxx .. 语句 2. 你的 insert into xxx ... 语句 3. 结果是什么样，（并给以简单的算法描述） 4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL) 这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。

.

[中国风]

引用 3 楼 gg_win 的回复:

楼上说的就是我用的方法，可看图一，行转列功能已经实现，正分值是会成功，可是负分值没有转行成功，给了一个0值

贴出语句,截图看不出语句

[gg_win]

DELIMITER $$ USE `ccandy`$$ DROP PROCEDURE IF EXISTS `no_time_select_score`$$ CREATE DEFINER=`ccandy`@`%` PROCEDURE `no_time_select_score`() BEGIN SET @sql = NULL; SELECT GROUP_CONCAT( DISTINCT CONCAT('MAX(IF(pro_t.func_code = ''',pro_t.func_code,''',pro_t.goal,0)) AS ''',pro_t.func_code,'''')) INTO @sql FROM ( SELECT aa.staff_id, aa.func_code , IFNULL(SUM(sr.goal),0)AS goal, aa.cloud_code, ssf.`NAME`, so.`FULL_NAME`, IFNULL(sct.info_code,0)AS info_code, IFNULL(sct.expr_goal,0)AS expr_goal, IFNULL(sct.app_goal,0)AS app_goal, IFNULL(sct.info_code,0)+IFNULL(sct.expr_goal,0)+IFNULL(sct.app_goal,0)AS allSum ,sge.name AS grade FROM (SELECT sc.staff_id, ss.func_code, ss.`cloud_code` FROM score_set ss, score_count sc WHERE ss.cloud_code="方案云" OR ss.cloud_code="专家云" OR ss.cloud_code="体验云" ORDER BY sc.staff_id )aa LEFT JOIN score_rec sr ON aa.staff_id = sr.staff_id AND aa.func_code = sr.func_code ,sys_staff_org sso, sys_org_map som, sys_org so, sys_staff ssf, score_count sct, score_grade sge WHERE 1=1 AND (IFNULL(sct.info_code,0)+IFNULL(sct.expr_goal,0)+IFNULL(sct.app_goal,0)) BETWEEN scope_start AND scope_end AND ssf.`ID`=aa.`staff_id` AND aa.`staff_id`=sct.`staff_id` AND ssf.org_id = sso.id AND sso.org_id = som.org_id AND som.PARENT_ID = so.ID AND CONTROL_LEVEL=200 GROUP BY aa.staff_id,aa.func_code ORDER BY aa.staff_id,aa.cloud_code,aa.func_code ) pro_t; SET @sql = CONCAT(' CREATE TEMPORARY TABLE newTable SELECT pro_t.staff_id, pro_t.NAME, pro_t.FULL_NAME, pro_t.fname, pro_t.info_code, pro_t.expr_goal, pro_t.app_goal, pro_t.allSum, pro_t.orgid1, pro_t.orgid2, pro_t.grade,', @sql, ' FROM ( SELECT aa.staff_id, aa.func_code , IFNULL(SUM(sr.goal),0)AS goal, aa.cloud_code, ssf.`NAME`, ( SELECT sos.FULL_NAME FROM sys_org_map soms, sys_org sos WHERE sso.ORG_ID = soms.ORG_ID AND soms.PARENT_ID = sos.ID AND sos.CONTROL_LEVEL = 200 ) FULL_NAME, ( SELECT sos.id FROM sys_org_map soms, sys_org sos WHERE sso.ORG_ID = soms.ORG_ID AND soms.PARENT_ID = sos.ID AND sos.CONTROL_LEVEL = 200 ) orgid1, ( SELECT sos.FULL_NAME FROM sys_org_map soms, sys_org sos WHERE sso.ORG_ID = soms.ORG_ID AND soms.PARENT_ID = sos.ID AND sos.CONTROL_LEVEL = 300 ) fname, ( SELECT sos.id FROM sys_org_map soms, sys_org sos WHERE sso.ORG_ID = soms.ORG_ID AND soms.PARENT_ID = sos.ID AND sos.CONTROL_LEVEL = 300 ) orgid2, so.id AS orgId, IFNULL(sct.info_code,0)AS info_code, IFNULL(sct.expr_goal,0)AS expr_goal, IFNULL(sct.app_goal,0)AS app_goal, IFNULL(sct.info_code,0)+IFNULL(sct.expr_goal,0)+IFNULL(sct.app_goal,0)AS allSum ,sge.name AS grade FROM(SELECT sc.staff_id, ss.func_code, ss.`cloud_code` FROM score_set ss, score_count sc WHERE ss.cloud_code="方案云" OR ss.cloud_code="专家云" OR ss.cloud_code="体验云" ORDER BY sc.staff_id )aa LEFT JOIN score_rec sr ON aa.staff_id = sr.staff_id AND aa.func_code = sr.func_code ,sys_staff_org sso, sys_org_map som, sys_org so, sys_staff ssf, score_count sct, score_grade sge WHERE 1=1 AND (IFNULL(sct.info_code,0)+IFNULL(sct.expr_goal,0)+IFNULL(sct.app_goal,0)) BETWEEN scope_start AND scope_end AND ssf.`ID`=aa.`staff_id` AND aa.`staff_id`=sct.`staff_id` AND ssf.org_id = sso.id AND sso.org_id = som.org_id AND som.PARENT_ID = so.ID AND CONTROL_LEVEL=200 GROUP BY aa.staff_id,aa.func_code ORDER BY aa.staff_id,aa.cloud_code,aa.func_code) pro_t group by staff_id; '); DROP TABLE IF EXISTS newTable; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; END$$ DELIMITER ; 行转列代码如上。 未转行时内容如下还有其他字段不一一列出，只写出功能要求字段： staff_id func_code goal 01 xx 28 01 yy 38 02 xx -33 02 yy 29 转行后我的代码效果如下： staff_id xx yy 01 28 38 02 0 29 应该是0的位置是个负值-33，不知到为什么会这样

[中国风]

GROUP_CONCAT( DISTINCT CONCAT('MAX(IF(pro_t.func_code = ''',pro_t.func_code,''',pro_t.goal,0)) AS ''',pro_t.func_code,'''')) 这一段要改改，问题出在MAX

[gg_win]

我也觉得是这段，也应该是这段，因为下面的查询表未转列前是有负值的，但能具体说明一下怎么改么？因为mysql行转列也是我百度来的，不知道怎么去改它

[中国风]

这样改测测 e.g.

GROUP_CONCAT( DISTINCT CONCAT('IFNULL(MAX(CASE WHEN pro_t.func_code = ''',pro_t.func_code,''' THEN pro_t.goal END),0) AS ''',pro_t.func_code,'''')) 

[gg_win]

引用 10 楼 roy_88 的回复:

这样改测测 e.g.

GROUP_CONCAT( DISTINCT CONCAT('IFNULL(MAX(CASE WHEN pro_t.func_code = ''',pro_t.func_code,''' THEN pro_t.goal END),0) AS ''',pro_t.func_code,'''')) 

可以了，谢谢大哥啦，