Mysql 动态实现行转列分析

最新推荐文章于 2024-07-22 03:17:47 发布

STRANG-P

最新推荐文章于 2024-07-22 03:17:47 发布

阅读量658

点赞数

分类专栏： MySQL 文章标签：列转行动态实现行转列存储过程循环拼接语句存储过程游标遍历 sql遍历

本文链接：https://blog.csdn.net/weixin_41922349/article/details/96993415

版权

MySQL 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

Mysql 动态\静态实现行转列

1、Sql 脚本实现行转列
2、存储过程动态实现

1、Sql 脚本实现行转列

创建表及准备测试数据 :

DROP TABLE IF EXISTS `student_scores`;
CREATE TABLE `student_scores` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL COMMENT '姓名',
  `score` double DEFAULT NULL COMMENT '分数',
  `subject` varchar(32) DEFAULT NULL COMMENT '科目',
  `class` varchar(32) DEFAULT NULL COMMENT '班级',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of student_scores
-- ----------------------------
INSERT INTO `student_scores` VALUES ('1', '陈诚', '68', '英语', '一年级');
INSERT INTO `student_scores` VALUES ('2', '陈诚', '98', '数学', '一年级');
INSERT INTO `student_scores` VALUES ('3', '陈诚', '86', '语文', '一年级');
INSERT INTO `student_scores` VALUES ('4', '张鹤', '87', '英语', '一年级');
INSERT INTO `student_scores` VALUES ('5', '张鹤', '100', '数学', '一年级');
INSERT INTO `student_scores` VALUES ('6', '张鹤', '97', '语文', '一年级');

执行查询 :
在这里插入图片描述

实现行转列 :


select t.name, 
  max(case when t.subject = '英语' then t.score else 0 END) as '英语',
  max(case when t.subject = '数学' then t.score else 0 END) as '数学',
  max(case when t.subject = '语文' then t.score else 0 END) as '语文'
from student_scores t GROUP BY t.name;

执行查询：

在这里插入图片描述

2、存储过程动态实现

说明 :

1、使用光标（游标），循环动态拼接 sql。
2、存储过程动态行转列。
3、用于示例，使用光标动态赋值，循环拼接。
4、适用于 MySQL数据库，且5.6及以上版本。

表结构：
在这里插入图片描述

存储过程脚本 :

DELIMITER //
CREATE PROCEDURE  getAnnounceInfo(IN stuName VARCHAR(100))
  BEGIN
  # 声明局部变量
  DECLARE sq VARCHAR(4000);
  DECLARE var_subject VARCHAR(20);
  # 游标循环控制变量
  DECLARE done INT DEFAULT 0;

  # 声明光标
  DECLARE	lins CURSOR FOR SELECT DISTINCT subj FROM t_users;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  set sq = 'SELECT stu_name';
  
   # 打开光标
   OPEN lins ;
   # 循环光标
   REPEAT
      FETCH lins INTO var_subject;
      IF NOT done THEN
        # 动态拼接 sql
        SET sq = CONCAT(sq, 
           ' ,MAX(CASE WHEN subj = "',var_subject, 
           '" THEN score ELSE 0 END ) as "',var_subject,'"');
        SET done = 0;
      END IF;
   UNTIL done END REPEAT; # 循环结束
   # 关闭光标
   CLOSE lins;

 IF stuName IS NOT NULL  THEN
  set sq = CONCAT(sq,' FROM t_users where stu_name = "', 
                  stuName,'" GROUP BY stu_name;');
 ELSE 
  set sq = CONCAT(sq,' FROM t_users GROUP BY stu_name;');
 END IF;
 SET @score_sql = sq;

 # 预编译动态sql, 该地方必须使用 from @参数, 如直接使用 from sq 报错。
 PREPARE sqa from @score_sql;
 # 执行动态 sql
 EXECUTE sqa ;
 # 删除预编译的 sql
 DEALLOCATE PREPARE sqa;
END // 
DELIMITER ;