mysql动态列存储过程

CREATE PROCEDURE `GetResultShow`(
evaluateId varchar(100)
)
begin
SET SESSION group_concat_max_len = 102400000;
 -- 保存生成的动态列
-- >>>>>>>>>>>>>>>>>>>>>>>
  DELETE from t_indexname_evaluate t where t.evaluate_id=evaluateId;
    insert into t_indexname_evaluate
    SELECT
          c.INDEX_NAME as index_name,
                    evaluateId as evaluate_id,
          CONCAT('a',rownum) as index_name_num
    FROM
        (select t.*,@rownum:=@rownum+1 AS rownum from
 (select q.* from (select DISTINCT INDEX_NAME, index_id,'3' as INDEX_LEVEL from index_score s
    WHERE s.EVALUATE_id = evaluateId
    UNION ALL
    select DISTINCT INDEX_NAME,index_id,INDEX_LEVEL from index_total_score t
    WHERE t.EVALUATE_id = evaluateId) q ORDER BY q.INDEX_LEVEL DESC) t,(SELECT @rownum:=0) r) c;

-- 拼接动态列
-- >>>>>>>>>>>>>>>>>>>>>>>
    SET @sql = NULL;
  SELECT
      GROUP_CONCAT(DISTINCT
        CONCAT(
          'MAX(IF(c.INDEX_NAME = ''',
          c.INDEX_NAME,
          ''', c.SCORE, 0)) AS ''',
          index_name_num, ''''
        ))
       INTO @sql
    FROM
    t_indexname_evaluate c where c.evaluate_id=evaluateId;
    
    -- 拼接sql
    -- <<<<<<<<<<<<<<<<<<<<<<<<
    
    SET @sql = CONCAT('Select c.target_id, c.target_name, ', @sql,
                            ' from (SELECT  t.target_id,
            t.target_name,
            t.SCORE,
            t.INDEX_ID,
            t.INDEX_NAME,
            t.index_parent_id
                FROM index_score  t
            UNION ALL
                SELECT
              s.target_id,
            s.target_name,
            s.SCORE,
            s.INDEX_ID,
            s.INDEX_NAME,
            s.index_parent_id
                FROM index_total_score s
            ) c
       group by target_id,target_name ');
        PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END