mysql横表和纵表以及互转实例

2019独角兽企业重金招聘Python工程师标准>>>

一.纵表

      

  相关建表语句：

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `username` varchar(50) DEFAULT NULL COMMENT '姓名',
  `course` varchar(50) DEFAULT NULL COMMENT '科目',
  `grade` double DEFAULT NULL COMMENT '分数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='纵表'

INSERT INTO `table1` (username,course,grade) VALUES('张三','语文',90);
INSERT INTO `table1` (username,course,grade) VALUES('李四','英语',88);
INSERT INTO `table1` (username,course,grade) VALUES('王五','数学',60);
INSERT INTO `table1` (username,course,grade) VALUES('张三','英语',120);

二.纵表转横表

      纵表转横表后结果：

相关sql语句：

SELECT username,
SUM(CASE course WHEN '语文' THEN grade ELSE 0 END) AS 'chinese',
SUM(CASE course WHEN '数学' THEN grade ELSE 0 END) AS 'marth',
SUM(CASE course WHEN '英语' THEN grade ELSE 0 END) AS 'english'           
FROM Table1 
GROUP BY username

把转换后的结果创建为一个新表，相关sql：

CREATE TABLE table2
SELECT id id,username,
SUM(CASE course WHEN '语文' THEN grade ELSE 0 END) AS 'chinese',
SUM(CASE course WHEN '数学' THEN grade ELSE 0 END) AS 'marth',
SUM(CASE course WHEN '英语' THEN grade ELSE 0 END) AS 'english'           
FROM Table1 
GROUP BY username;
ALTER TABLE table2 COMMENT '横表';

三.横表

     此时，把简单的横表数据结果，调整为如下截图所示：

相关sql代码：

UPDATE `table2` SET chinese = 80,marth = 90,english=70 WHERE id = 1;
UPDATE `table2` SET chinese = 90,marth = 85,english=95 WHERE id = 2;
UPDATE `table2` SET chinese = 88,marth = 75,english=90 WHERE id = 3;

四.横表转纵表

横表转纵表结果：      

相关sql代码：

SELECT username,'chinese' AS course,chinese AS grade FROM table2 
UNION ALL
SELECT username,'marth' AS course,marth AS grade FROM table2 
UNION ALL
SELECT username,'english' AS course,english AS grade FROM table2
ORDER BY username,course DESC

规则：

case 变量表达式              --对某个‘变量表达式’进行判断
when 值                      --当‘变量表达式’是某个‘值’时
then 返回值表达式            --返回‘返回值表达式’值
[when...
then...
.....]                       --可以进行多次判断
[else 其他情况返回值表达式]  --不符合所有when后面的就是其他情况了
end                          --结束

五.横表与纵表执行效率比

横表:后台数据库管理员操作简单，直观，清晰可见，一目了然。但若要给横表中添加一个或者多个字段，就须重建表结构。

纵表:对于横表的弊端，纵表中只需要添加一条记录，就可以添加一个字段，所消耗的代价远比横表小。但是纵表的对于数据描述不是很清晰，而且会造成数据库数量很多。在查询的时候用到group等函数会大大降低执行效率。纵表的初始映射要慢一些，纵表的变更的映射可能要快一些，如果只是改变了单个字段时，毕竟横表字段比纵表要多很多。

六.转换的场景

1.在平时的开发过程中，可能会遇到字段的添加或者更好的维护和管理大数据量的表，就 会涉及到纵表和横表之间的转换。

2.把不容易改动表结构的设计成横表，把容易经常改动不确定的表结构设计成纵表。

转载于:https://my.oschina.net/xsh1208/blog/653801