Mysql横纵表转换实例

Mysql横纵表转换实例

    在实际业务中，有时需要将横表数据转换成纵表数据操作或者将纵表数据转换为横表数据，此文是SQL操作实例，希望能够帮助到有需要的同学；

一、横表数据转纵表

1.建表DDL

CREATE TABLE `subject` (
	`id` INT ( 11 ) NOT NULL,
	`name` VARCHAR ( 50 ) DEFAULT NULL,
	`chinese` VARCHAR ( 50 ) DEFAULT NULL,
	`math` VARCHAR ( 50 ) DEFAULT NULL,
	`english` VARCHAR ( 50 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8;

2.插入测试数据

INSERT INTO ` studysql `.` subject ` ( ` ID `, ` NAME `, ` chinese `, ` math `, ` english ` )
VALUES
	( 1, '小张', '80', '80', '90' );
INSERT INTO ` studysql `.` subject ` ( ` ID `, ` NAME `, ` chinese `, ` math `, ` english ` )
VALUES
	( 2, '小红', '70', '60', '50' );
INSERT INTO ` studysql `.` subject ` ( ` ID `, ` NAME `, ` chinese `, ` math `, ` english ` )
VALUES
	( 3, '小李', '80', '95', '96' );
INSERT INTO ` studysql `.` subject ` ( ` ID `, ` NAME `, ` chinese `, ` math `, ` english ` )
VALUES
	( 4, '肖哥', '6', '6', '6' );
INSERT INTO ` studysql `.` subject ` ( ` ID `, ` NAME `, ` chinese `, ` math `, ` english ` )
VALUES
	( 5, '鸡哥', '99', '9', '9' );

3. 横表转纵表SQL

SELECT
	* 
FROM
	(
	SELECT SUBJECT
		.NAME AS '姓名',
		'语文',
		SUBJECT.chinese AS '成绩' 
	FROM
	SUBJECT UNION
	SELECT SUBJECT
		.NAME AS '姓名',
		'数学',
		SUBJECT.math AS '成绩' 
	FROM
	SUBJECT UNION
	SELECT SUBJECT
		.NAME AS '姓名',
		'英语',
		SUBJECT.english AS '成绩' 
	FROM
	SUBJECT 
	) SUBJECT 
ORDER BY
	SUBJECT.姓名

4. 执行结果

二、纵表数据转横表

1.建表DDL

CREATE TABLE ` subject01 ` (
` ID ` INT ( 11 ) NOT NULL,  ` NAME ` VARCHAR ( 50 ) DEFAULT NULL,  ` subject ` VARCHAR ( 50 ) DEFAULT NULL,  ` score ` VARCHAR ( 50 ) DEFAULT NULL,
PRIMARY KEY ( ` ID ` )) ENGINE = InnoDB DEFAULT CHARSET = utf8;

2.插入测试数据

INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 1, '小芳', '语文', '90' );
INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 2, '小李', '数学', '80' );
INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 3, '小芳', '英语', '88' );
INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 4, '小芳', '英语', '99' );
INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 5, '小黑', '语文', '55' );
INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 6, '小李', '语文', '88' );
INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 7, '小李', '英语', '55' );
INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 8, '小黑', '数学', '20' );
INSERT INTO ` studysql `.` subject01 ` ( ` ID `, ` NAME `, ` subject `, ` score ` )
VALUES
	( 9, '小黑', '英语', '55' );

3.纵表转横表SQL

SELECT
	subject01.NAME AS '姓名',
	MAX ( CASE subject01.subject WHEN '语文' THEN score ELSE 0 END ) 语文,
	MAX ( CASE subject01.subject WHEN '英语' THEN score ELSE 0 END ) 英语,
	MAX ( CASE subject01.subject WHEN '数学' THEN score ELSE 0 END ) 数学 
FROM
	SUBJECT01 
GROUP BY
	subject01.NAME

4.执行结果