mysql行转列（转置）的方式

最新推荐文章于 2025-05-03 09:07:35 发布

夜晚不开灯

最新推荐文章于 2025-05-03 09:07:35 发布

阅读量2k

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/qq_35072197/article/details/129025209

版权

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了MySQL中实现行转列的多种方法，包括使用CASE...WHEN...THEN、IF函数以及SUM(IF())结合动态列生成。详细讲解了每种方法的使用场景和注意事项，并给出了相关示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、初始化

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) COMMENT '科目',
    score DOUBLE COMMENT '成绩',
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;


--- 插入数据

INSERT INTO tb_score(userid,subject,score) VALUES ('001','语文',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);
INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','语文',88);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','语文',70);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);
INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);

2、select * from tb_score

数据内容为

3、行转列方式

1）使用使用case…when…then 进行行转列

SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治'
FROM tb_score
GROUP BY userid

sum() 聚合函数，可以配合 case when then else end实现行列转置

MySQL 的 case when 的语法有两种：

简单函数 
CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
搜索函数 
CASE WHEN [expr] THEN [result1]…ELSE [default] END

CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END：枚举这个字段所有可能的值

SELECT
NAME '英雄',
CASE NAME
WHEN '德莱文' THEN
'斧子'
WHEN '德玛西亚-盖伦' THEN
'大宝剑'
WHEN '暗夜猎手-VN' THEN
'弩'
ELSE
'无'
END '装备'
FROM
user_info;

CASE WHEN [expr] THEN [result1]…ELSE [default] END：搜索函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略

# when 表达式中可以使用 and 连接条件
SELECT
NAME '英雄',
age '年龄',
CASE
WHEN age < 18 THEN
'少年'
WHEN age < 30 THEN
'青年'
WHEN age >= 30
AND age < 50 THEN
'中年'
ELSE
'老年'
END '状态'
FROM
user_info;

参考文章： https://www.cnblogs.com/chenduzizhong/p/9590741.html

2)使用IF进行行列转置

SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治'
FROM tb_score
GROUP BY userid

注意点：

（1）SUM() 是为了能够使用GROUP BY根据userid进行分组，因为每一个userid对应的subject="语文"的记录只有一条，所以SUM() 的值就等于对应那一条记录的score的值。

假如userid =‘001’ and subject=‘语文’ 的记录有两条，则此时SUM() 的值将会是这两条记录的和，同理，使用Max()的值将会是这两条记录里面值最大的一个。但是正常情况下，一个user对应一个subject只有一个分数，因此可以使用SUM()、MAX()、MIN()、AVG()等聚合函数都可以达到行转列的效果。

（2）IF(subject=‘语文’,score,0) 作为条件，即对所有subject='语文’的记录的score字段进行SUM()、MAX()、MIN()、AVG()操作，如果score没有值则默认为0。

（3）IF()函数在条件为TRUE时返回一个值，如果条件为FALSE则返回另一个值。

IF(condition, value_if_true, value_if_false)

SELECT IF(500<1000, "YES", "NO");

3)利用SUM(IF()) 生成列，直接生成结果

SELECT IFNULL(userid,'TOTAL') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(score) AS TOTAL
FROM tb_score
GROUP BY userid WITH ROLLUP;

在这里插入图片描述

4)动态，适用于列不确定

SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;

SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
-- SELECT @QQ;

PREPARE stmt FROM @QQ;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

（1）@的作用

用户变量：以"@"开始，形式为"@变量名"。用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效
全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名或者 set @@global.变量名，对所有客户端生效。只有具有super权限才可以设置全局变量
会话变量：只对连接的客户端有效。
局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

参考链接：https://www.cnblogs.com/zzn1/p/16094108.html

（2）prepare execute 关键词

语法
PREPARE 预处理语句名 FROM '预处理的sql'； //准备

EXECUTE 预处理语句名 USING 注入参数1，注入参数2； //执行

DEALLOCATE PREPARE 预处理语句名;   //释放

说明
预处理的sql中，sql语句中用？代表参数。第一个问号，等会儿用注入参数1注入，第二个问号，用注入参数2注入

例子
PREPARE stm001 FROM 'select * from 表 where sex=? and name=?'；

EXECUTE stm001 USING '男'，'张三'；

DEALLOCATE PREPARE stm001;

（3）：= f

只有在set和update时才是和:=一样，赋值的作用，其它都是等于的作用。

不只在set和update时时赋值的作用，在select也是赋值的作用。

验证“ := ”的作用

验证“ = ” 的作用

文章：MySQL中“:=”和“=”的区别_mysql:=_大林子先森的博客-优快云博客

（4）Concat函数

mysql CONCAT（）函数用于将多个字符串连接成一个字符串，是最重要的mysql函数之一，下面就将为您详细介绍mysql CONCAT（）函数，供您参考

mysql CONCAT(str1,str2,…)
返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

文章：https://www.cnblogs.com/aiyr/p/6830593.html

（5）运行结果

SET @EE='';
select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;
SELECT @EE

运行 select @EE

SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
SELECT @QQ;

运行 select @QQ