mysql sql列转行函数 mysql 行转列 列转行

mysql实现行转列,列转行 实操

一, 行转列

提示: 使用case…when或if语句分情况查询数据，group by和sum/max进行筛选

新建学生表,如下

CREATE TABLE `student` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `result` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入以下数据

INSERT INTO `mysqltest`.`student`(`id`, `name`, `subject`, `result`) VALUES (1, '张三', '语文', '80');
INSERT INTO `mysqltest`.`student`(`id`, `name`, `subject`, `result`) VALUES (2, '张三', '数学', '90');
INSERT INTO `mysqltest`.`student`(`id`, `name`, `subject`, `result`) VALUES (3, '张三', '物理', '95');
INSERT INTO `mysqltest`.`student`(`id`, `name`, `subject`, `result`) VALUES (4, '李四', '语文', '77');
INSERT INTO `mysqltest`.`student`(`id`, `name`, `subject`, `result`) VALUES (5, '李四', '数学', '56');
INSERT INTO `mysqltest`.`student`(`id`, `name`, `subject`, `result`) VALUES (6, '李四', '物理', '100');

1, 经典case when实现

SELECT 
	name,
	max( CASE subject WHEN '语文' THEN result ELSE 0 END ) '语文',
	max( CASE subject WHEN '数学' THEN result ELSE 0 END ) '数学',
	max( CASE subject WHEN '物理' THEN result ELSE 0 END ) '物理' 
FROM
	student 
GROUP BY name

结果展示:

2, 使用if语句

SELECT 
	name,
	max( IF ( subject = '语文', result, 0 ) ) '语文' ,
	max( IF ( subject = '数学', result, 0 ) ) '数学' ,
	max( IF ( subject = '物理', result, 0 ) ) '物理' 
FROM
	student 
GROUP BY name

结果展示:

3, 动态拼接sql语句

SET @SQL = NULL;
SELECT
	group_concat( DISTINCT concat( 'max(if(a.subject = ''', a.subject, ''', a.result, 0)) as ''', a.subject, '''' ) ) INTO @SQL 
FROM
	student a;
SET @SQL = concat( 'select name,', @SQL, 'from student a group by a.name' );
PREPARE temp 
FROM
	@SQL; -- 动态生成脚本，预备一个语句
EXECUTE temp; -- 动态执行脚本，执行预备的语句
DEALLOCATE PREPARE temp; -- 释放预备的语句

结果展示:

二, 列转行

新建class表,如下

CREATE TABLE `class` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `chinese` varchar(255) DEFAULT NULL COMMENT '语文',
  `math` varchar(255) DEFAULT NULL COMMENT '数学',
  `physics` varchar(255) DEFAULT NULL COMMENT '物理',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据:

INSERT INTO `mysqltest`.`class`(`id`, `name`, `chinese`, `math`, `physics`) VALUES (1, '张三', '80', '95', '66');
INSERT INTO `mysqltest`.`class`(`id`, `name`, `chinese`, `math`, `physics`) VALUES (2, '李四', '99', '88', '78');

将左侧的转为右侧的

*提示:使用union或者union all求并集加粗样式

SELECT 
	name,
	'语文' AS subject,
	chinese AS result 
	FROM class 
UNION ALL
SELECT 
	name,
	'数学' AS subject,
	math AS result 
	FROM class 
UNION ALL
SELECT 
	name,
	'物理' AS subject,
	physics AS result 
	FROM class

结果展示: