mysql中横竖转换_MySQL中的横竖表转换

前言：

横竖表转化，在我看来即是小型的动态数据库设计。将字段名作为值来操作，控制表中字段数量避免冗余、动态的设计数据库、方便日后我们操作及扩展。

示例：

首先创建一张竖表版的student(学生表)

DROP TABLE student;

CREATE TABLE student

(

id INT PRIMARY KEY AUTO_INCREMENT,

stu_name VARCHAR(50), -- 学生名 stu_subject VARCHAR(50), -- 科目 stu_score INT -- 分数);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('张三','语文',80);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('张三','数学',86);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('张三','英语',75);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('李四','语文',78);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('李四','数学',85);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('李四','英语',78);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('王五','语文',77);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('王五','数学',77);

INSERT INTO student(`stu_name`,`stu_subject`,`stu_score`) VALUES ('王五','英语',77);

SELECT * FROM student创建竖表后的查询结果

此时创建了一张竖表，我们将使用查询结果连接的方式将竖表其转换为横表。

-- 竖表转横表(使用连接)

CREATE TABLE student_across(

-- 注：此处为了方便演示用到了中文作为字段名，平时开发切记不能使用中文字段名

SELECT s1.stu_name,s1.stu_score AS '语文',s2.stu_score AS '数学',s3.stu_score AS '英语'

FROM

(SELECT stu_name,'语文',stu_score FROM student WHERE stu_subject ='语文')s1,

(SELECT stu_name,'数学',stu_score FROM student WHERE stu_subject ='数学')s2,

(SELECT stu_name,'英语',stu_score FROM student WHERE stu_subject ='英语')s3

WHERE s1.stu_name=s2.stu_name AND s2.stu_name=s3.stu_name

)

SELECT * FROM student_across;竖表转为横表的查询结果

此时可以看到，最次设计时如果我们将科目名称作为字段名的话，若有其它科目，则需重新添加字段。如：物理要增加一个字段、体育要增加一个字段、化学要增加一个字段.........此时的字段名将越来越多。得不到有效得扩展，且字段超过一定数量后，我们查阅操作起来不方便，也有违数据库设计规范。

此时创建了一张横表，我们将使用查询结果相加的方式将横表其转换为竖表。

-- 横表转竖表(使用结果集相加)

-- 注：此处为了方便演示用到了中文作为字段名，平时开发切记不能使用中文字段名

SELECT stu_name,'语文' AS 'stu_subject',语文 AS stu_score FROM student_across

UNION

SELECT stu_name,'数学' AS 'stu_subject',数学 AS stu_score FROM student_across

UNION

SELECT stu_name,'英语' AS 'stu_subject',英语 AS stu_score FROM student_across横表转换为竖表

不周之处，请多多指出。一起学习，共同进步！