MySQL多种行转列方法

方法1.case ... when ... then ... ELSE '' END

参考文章：https://blog.csdn.net/ml1990s/article/details/16953999

表

SQL语句

SELECT brand_name,
MAX(case level_code when 'LV1' then task_limit ELSE '' END) AS 'LV1',
MAX(case level_code when 'LV2' then task_limit ELSE '' END) AS 'LV2',
MAX(case level_code when 'LV3' then task_limit ELSE '' END) AS 'LV3' 
FROM t_wd_task_limi GROUP BY brand_id

查询结果

 

方法2.存储过程：行转列

表：

存储过程代码：

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(c.level_code = ''',
      c.level_code,
      ''', s.task_limit, '''')) AS ''',
      c.level_code, ''''
    )
  ) INTO @sql
FROM t_wd_proxy_leve c WHERE c.delete_flag='1' ORDER BY c.sort_no;
 
SET @sql = CONCAT('Select s.brand_id AS id, s.brand_name, ', @sql, 
                        ' From t_wd_task_limi s 
                        Left Join t_wd_proxy_leve c On c.level_code = s.level_code');
 
SET @sql = CONCAT(@sql, ' Group by s.brand_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

查询结果：