文档章节

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

谢思华
 谢思华
发布于 2016/04/06 18:07
字数 778
阅读 2208
收藏 12

一.纵表

      

  相关建表语句:

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.把不容易改动表结构的设计成横表,把容易经常改动不确定的表结构设计成纵表。

© 著作权归作者所有

谢思华
粉丝 71
博文 216
码字总数 152149
作品 0
广州
程序员
私信 提问
Oracle 横表/纵表用场以及它们之间的相互转换

一、横表和纵表 横表:通常指我们平时在数据库中建立的表,是一种普通的建表方式。 (主键、字段1、字段2......)如:时间、客户ID,基本通话费、漫游通话费,国内长途费、国际长途费....。 ...

指尖的舞者
2012/08/05
0
0
数据库表为纵表时转变为横表的设计方法

一. 横表&纵表: 数据库表按照存储的数据结构不同区分为横表与纵表,通过如下数据的存储我们分别对横表与纵表进行简单介绍: 横表: http://www.finereport.com/forumimages/zjkbwzbszbwhb1...

finereport
2011/11/24
0
0
阿里年薪50WJAVA工程师转大数据学习路线!

大数据有两个方向,一个是偏计算机的,另一个是偏经济的。你学过Java,所以你可以偏将计算机的。 Java程序员想转大数据可行吗?Java是全世界使用人数最多的编程语言。不少程序员选择Java做为...

JAVA丶学习
2018/04/25
0
0
使用 Informatica 做将字符串转换成日期

数据源为 SQLServer 数据库,且源字段 varchar 类型。需要抽取到 Oracle 数据库中,并且转换为 date 类型。 由于源表和目标表是两种不同的数据库,且数据类型不同,所以无法在源表限定转换器...

DreamOver
2018/06/08
0
0
基于ESB实现商友与K3财务凭证集成

背景 沈阳XX商场推行O2O营销模式,信息化系统包括:线下零售系统、线上电商系统、网上支付系统,财务核算系统,为了实现线上线下系统互动、财务核算一体化,需要对各系统进行数据集成、系统对...

数通畅联
2014/12/10
0
0

没有更多内容

加载失败,请刷新页面

加载更多

texlive安装

Installing to: D:/bin/texlive/texlive/2019Installing [001/307, time/total: ??:??/??:??]: adobemapping [2130k]Installing [002/307, time/total: 00:03/08:57]: ae [84k]Installing......

MtrS
今天
2
0
运维规范

命名规范 发布流程 监控告警 故障定位 状态 日志 监控

以谁为师
今天
2
0
约瑟夫环(报数游戏)java实现

开端 公司组织考试,一拿到考题,就是算法里说的约瑟夫环,仔细想想 以前老师将的都忘了,还是自己琢磨把~ package basic.gzy;import java.util.Iterator;import java.util.LinkedList;...

无极之岚
今天
3
0
Kernel字符设备驱动框架

Linux设备分为三大类:字符设备,块设备和网络设备,这三种设备基于不同的设备框架。相较于块设备和网络设备,字符设备在kernel中是最简单的,也是唯一没有基于设备基础框架(device结构)的...

yepanl
今天
3
0
Jenkins 中文本地化的重大进展

本文首发于:Jenkins 中文社区 我从2017年开始,参与 Jenkins 社区贡献。作为一名新成员,翻译可能是帮助社区项目最简单的方法。 本地化的优化通常是较小的改动,你无需了解项目完整的上下文...

Jenkins中文社区
昨天
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部