不使用union实现Mysql 列转行

最近工作上用到了 mysql列转行，网上找了一堆大多数都是行转列的方法，对于列转行这块，仅找到了union这一种方式，偏偏工作环境的数据库版本较低不支持临时表，使用union方式写起来又过于笨重，所以这里记录下使用union及不使用union的列转行解决办法

注：未比较两种方式执行效率

测试数据：

CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `chinese` int DEFAULT NULL,
  `math` int DEFAULT NULL,
  `english` int DEFAULT NULL,
  `wuli` int DEFAULT NULL,
  `huaxue` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO test.test (id, name, chinese, math, english, wuli, huaxue) VALUES(1, '张三', 110, 120, 85, NULL, NULL);
INSERT INTO test.test (id, name, chinese, math, english, wuli, huaxue) VALUES(2, '李四', 130, 88, 89, NULL, NULL);
INSERT INTO test.test (id, name, chinese, math, english, wuli, huaxue) VALUES(3, '王五', 93, 124, 87, 98, 67);

基本的union 列转行写法：

-- 第一种使用union实现列传行
select name,'语文' as course, chinese as 'score' from test union 
select name,'数学' as course, math as 'score' from test union 
select name,'英语' as course, english as 'score' from test union 
select name,'物理' as course, wuli as 'score' from test union 
select name,'化学' as course, huaxue as 'score' from test order by name asc

实现效果：

不使用union 列转行写法：

不用union的方式实现思路：

列转行主要核心就是一条数据转多条，如果不多次查询，又想实现列转行，就需要先对数据按照我们的查询需求进行条数扩充；同时为了保证扩充结果可以满足我们后续提取数据的需要，所以扩充的同时需要往数据中填充字段名用于字段提取。

-- 借助系统表（或自己构造临时数据）用join 扩增数据，然后使用case when 实现列转行，获取想要的结果集。
select  
	b.name,
	a.COLUMN_NAME as 科目,
	case a.COLUMN_NAME when 'chinese' then b.chinese when 'math' then b.math
	     when 'english' then b.english when 'wuli' then b.wuli  when 'huaxue' then b.huaxue end as 成绩
from 
-- 借助系统表获取字段清单，添加自定义joinid用于数据关联
	(select  COLUMN_NAME ,'aa' as joinid from  information_schema.`COLUMNS` c  where TABLE_NAME ='test' and column_name not in ('id','name')) a 
-- 使用自定义joinid  join  获取笛卡尔积结果集
	left  join 
-- 查询源表，添加自定义joinid用于数据关联
	(select *,'aa' as joinid from test t ) b 
	on a.joinid=b.joinid order by b.name

过程展示：

通过系统表获取字段名，同时补充自定义关联列：

使用join关联字段名表和数据表，完成数据构造：

使用case when 提取想要的数据，达到列转行的目标：