Mysql行转列，列转行----存储过程实现

最新推荐文章于 2021-02-02 20:32:54 发布

qq_35729915

最新推荐文章于 2021-02-02 20:32:54 发布

阅读量734

点赞数

CC 4.0 BY-SA版权

文章标签： SQL

本文链接：https://blog.youkuaiyun.com/qq_35729915/article/details/88945359

本文介绍了如何使用MySQL的存储过程来实现数据的行转列和列转行操作。通过创建存储过程，将示例数据表中的学生科目成绩以行列互换的形式展示。详细步骤和示例代码参考了相关博客资源。

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

1.行转列存储过程实现方式
准备数据：
CREATE TABLE studentscores (
username varchar(20) COLLATE utf8_bin DEFAULT NULL,
subjectname varchar(30) COLLATE utf8_bin DEFAULT NULL,
score double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC;
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘张三’, ‘语文’, 80);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘张三’, ‘数学’, 90);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘张三’, ‘英语’, 70);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘张三’, ‘生物’, 85);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘李四’, ‘语文’, 80);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘李四’, ‘数学’, 92);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘李四’, ‘英语’, 76);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘李四’, ‘生物’, 88);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘码农’, ‘语文’, 60);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘码农’, ‘数学’, 82);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘码农’, ‘英语’, 96);
INSERT INTO crm.studentscores(username, subjectname, score) VALUES (‘码农’, ‘生物’, 78);
得到如下结果集：
在这里插入图片描述
通过存储过程实现行转列：

CREATE PROCEDURE test1()
BEGIN
	#课程名称
  DECLARE cname_n VARCHAR (20) ; #所有课程数量
	DECLARE count INT ; #计数器
	DECLARE i INT DEFAULT 0 ; #拼接SQL字符串
			SET @s = 'SELECT username' ;
			SET count = (          #计算总课程数量
				SELECT
					COUNT(DISTINCT subjectname)
				FROM
					studentscores
			) ;
			WHILE i < count DO
			SET cname_n = (
				SELECT
					subjectname
				FROM
					studentscores
				GROUP BY subjectname 
				LIMIT i,1
			) ;
			SET @s = CONCAT(
				@s,
				', SUM(CASE subjectname WHEN ',
				'\'',
				cname_n,
				'\'',
				' THEN score ELSE 0 END)',
				' AS ',
				'\'',
				cname_n,
				'\''
			) ;
			SET i = i + 1 ;
			END
			WHILE ;
			SET @s = CONCAT(
				@s,
				' FROM studentscores GROUP BY username'
			) ; #用于调试
			#SELECT @s;
			PREPARE stmt
			FROM
				@s ;
			EXECUTE stmt ;
			END
CALL test1();
#删除存储过程
DROP PROCEDURE test1;

参考博客：
https://blog.youkuaiyun.com/u013938484/article/details/50552747
https://blog.youkuaiyun.com/testcs_dn/article/details/49847299