mysql 列转行 动态写法

-- 列转行动态sql   
--  测试表

DROP TABLE IF EXISTS `tb_score`;
CREATE TABLE `tb_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` varchar(20) NOT NULL COMMENT '用户id',
  `subject` varchar(20) DEFAULT NULL COMMENT '科目',
  `score` double DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
)； 

INSERT INTO `tb_score` VALUES ('1', '001', '语文', '90');

INSERT INTO `tb_score` VALUES ('2', '001', '数学', '92');

INSERT INTO `tb_score` VALUES ('3', '001', '英语', '80');

INSERT INTO `tb_score` VALUES ('4', '002', '语文', '88');

INSERT INTO `tb_score` VALUES ('5', '002', '数学', '90');

INSERT INTO `tb_score` VALUES ('6', '002', '英语', '75.5');

INSERT INTO `tb_score` VALUES ('7', '003', '语文', '70');

INSERT INTO `tb_score` VALUES ('8', '003', '数学', '85');

INSERT INTO `tb_score` VALUES ('9', '003', '英语', '90');

INSERT INTO `tb_score` VALUES ('10', '003', '政治', '82');

-- 查询结果
Select * from tb_score; 

我们想要的结果---------

可以使用以下sql

select b.userid,
sum(case `subject` when '语文' then score else 0 end) '语文',
sum(case `subject` when '数学' then score else 0 end) '数学',
sum(case `subject` when '英语' then score else 0 end) '英语',
sum(case `subject` when '政治' then score else 0 end) '政治'
from tb_score b GROUP BY userid;

以上代码 的缺点是 需要明确知道科目 的种类，且科目过多 sql 会越来越长，主要是  sum(case `subject` when '语文' then score else 0 end) '语文', 这一段 需要拼接，改为动态拼接

先拼接这一段

 

-- 1、申明@sql
set @sql=null;
-- 2、拼接
select   GROUP_CONCAT(DISTINCT CONCAT('sum(case subject',' when ''',subject,''' then score else 0 end) as ''',subject,''''))  into @sql from tb_score;
-- 3、查看拼接结果
select @sql;

 

 

动态拼接 不管 subject 有多少 sql 语句不会增加....

继续拼接成 以下 得到我们需要的结果

 

 拼接语句

set @sql =CONCAT('select b.userid,',@sql,'from tb_score b GROUP BY userid');
-- 查看拼接结果
select @sql

最终得到sql

此时得到sql 并不能直接运行 需要使用  PREPARE 执行

-- 使用预执行 PREPARE 声明 stmt
PREPARE stmt FROM @sql;
-- 开始执行
EXECUTE stmt;
-- 清除
DEALLOCATE PREPARE stmt;

 

完整代码

set @sql=null;
-- 拼接
select   GROUP_CONCAT(DISTINCT CONCAT('sum(case subject',' when ''',subject,''' then score else 0 end) as ''',subject,''''))  into @sql from tb_score;
-- 查看拼接结果
select @sql;

set @sql =CONCAT('select b.userid,',@sql,'from tb_score b GROUP BY userid');
SELECT @sql;

-- 使用预执行 PREPARE 声明 stmt
PREPARE stmt FROM @sql;
-- 开始执行
EXECUTE stmt;
-- 清除
DEALLOCATE PREPARE stmt;