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;

Mysql 列转行动态写法

 

创建表 tb_score

DROP TABLE IFEXISTS `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`)

) ENGINE=InnoDBAUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

 

-- ----------------------------

-- 插入语句

-- ----------------------------

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  就可以得到上面的结果。。。 但是 如果 科目不确定或者有很多，那么此sql 就会显得臃肿

select b.userid,

sum(case `subject`when '语文' then score else0 end) '语文',

sum(case `subject`when '数学' then score else0 end) '数学',

sum(case `subject`when '英语' then score else0 end) '英语',

sum(case `subject`when '政治' then score else0 end) '政治'

from tb_score bGROUP BY userid;

 

--  主要问题是 需要拼接的部分为   sum(case `subject` when '政治' then score else 0 end) '政治'

-- 1、申明@sql

set @sql=null;

-- 2、拼接

select   GROUP_CONCAT(DISTINCT CONCAT('sum(casesubject',' when ''',subject,''' then score else 0 end) as''',subject,''''))  into @sql fromtb_score;

-- 3、查看拼接结果

select @sql;

我们需要的结果---- 由动态拼接获得如下部分，我们不需要知道subject 有多少种，这就是动态sql 的好处

sum(case subjectwhen '语文' then score else0 end) as '语文',

sum(case subjectwhen '数学' then score else0 end) as '数学',

sum(case subjectwhen '英语' then score else0 end) as '英语',

sum(case subjectwhen '政治' then score else0 end) as '政治'

 

继续 我们最终要得到的sql是这种样式，那么就要继续拼接 selet b.userid  和 from tb_score bGROUP BY userid

 

select b.userid,

sum(case `subject`when '语文' then score else0 end) '语文',

………………………………………………………………………….

from tb_score bGROUP BY userid;

 

 

-- 继续拼接 语句

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

-- 使用预执行 PREPARE 声明 stmt

PREPARE stmt FROM@sql;

-- 开始执行

EXECUTE stmt;

-- 清除

DEALLOCATE PREPAREstmt;

 

结果如下。。。。。

 

 

完整sql--------------------------------------------------------

-- 申明@sql

set @sql=null;

-- 拼接

select   GROUP_CONCAT(DISTINCT CONCAT('sum(casesubject',' when ''',subject,''' then score else 0 end) as''',subject,''''))  into @sql fromtb_score;

-- 查看拼接结果

-- select @sql;

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

-- 使用预执行 PREPARE 声明 stmt

PREPARE stmt FROM@sql;

-- 开始执行

EXECUTE stmt;

-- 清除

DEALLOCATE PREPAREstmt;

-----------------------------------------------------------------------------------------