我正在使用光标来构建准备好的语句,而且效果很好(实际上比预期的要好)。
,但我无法将头缠绕在最后一行。挑战"我现在想称呼它。
我需要在语句的最后一行中添加假选项,以使查询不会以" semicolon"结尾。
OPEN cEmployeeSkill;
-- loop through temp table
getLine: LOOP
FETCH cEmployeeSkill INTO vSkill;
IF vFinished = 1 THEN
SET @StatementTemp = CONCAT('MAX(CASE WHEN Skill_id = 0 THEN SkillLevelName END) AS Dummy' ');
SET @StatementMain = CONCAT(@StatementMain,@StatementTemp);
LEAVE getLine;
END IF;
-- get the current title of the skill
SET @ColumnTitle = (SELECT SkillName FROM tbl_Skill WHERE Skill_id = vSkill GROUP BY SkillName LIMIT 1);
-- do the pivot thing
SET @StatementTemp = CONCAT('MAX(CASE WHEN Skill_id = ',vSkill,' THEN SkillLevelName END) AS '',@ColumnTitle,'', ');
-- add the new line to the statment
SET @StatementMain = CONCAT(@StatementMain,@StatementTemp);
END LOOP getLine;
CLOSE cEmployeeSkill;
关于如何避免添加"虚拟"列的任何想法?
最后,只需切断最后两个字符即可失去最后一个逗号:
SET @StatementMain = LEFT(@StatementMain, LEN(@StatementMain)-2);