MySQL横表和纵表的相互转换

在应用中为了从不同的视图去分析数据，会使用不同的方案去查询数据库，横表和纵表的相互转换就是其中一个常见的情景。

横表变纵表

横表变纵表的技术要领是多次查询然后 union到一个视图里面。举个栗子，表tb1有如下数据：

id name gender origin_province birthday 1 Xie Fei male Hubei 1986-09-11 2 Wang Chuan female Shanxi 1978-02-27 3 Liu Xueying female Guizhou 1990-11-02

现在要求写出查询语句得到如下查询结果:

id name property value 1 Xie Fei gender male 1 Xie Fei origin_province Hubei 1 Xie Fei birthday 1986-09-11 2 Wang Chuan gender female 2 Wang Chuan origin_province Shanxi 2 Wang Chuan birthday 1978-02-27 3 Liu Xueying gender female 3 Liu Xueying origin_province Guizhou 3 Liu Xueying birthday 1990-11-02

我们首先来建表啦~：

CREATE TABLE `tb1`(    id int(11) PRIMARY KEY auto_increment,     name varchar(255) not null,    gender varchar(8) not null CHECK (gender in ('male','female')),    origin_province varchar(255) not null,    birthday date not null)    ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `tb1` (`name`, `gender`, `origin_province`, `birthday`) VALUES('Xie Fei', 'Male', 'Hubei', '1986-09-11'), ('Wang Chuan', 'Females', 'Shanxi', '1978-02-27'),('Liu Xueying', 'Females', 'Guizhou', '1990-11-02');

表建好啦，现在我们来查询：

sql语句如下：

select id as id, name as name, 'gender' as property, gender as value from tb1unionselect id as id, name as name, 'origin_province' as property, origin_province as value from tb1unionselect id as id, name as name, 'birthday' as property, birthday as value from tb1order by id, property;

结果如下：

纵表变横表

纵表变横表的技术要领是case when关键词。举个栗子，表tb2有如下数据：

id name property value 1 Xie Fei gender male 2 Xie Fei origin_province Hubei 3 Xie Fei birthday 1986-09-11 4 Wang Chuan gender female 5 Wang Chuan origin_province Shanxi 6 Wang Chuan birthday 1978-02-27 7 Liu Xueying gender female 8 Liu Xueying origin_province Guizhou 9 Liu Xueying birthday 1990-11-02

通过查询得到如下数据：

id name gender origin_province birthday 1 Xie Fei male Hubei 1986-09-11 2 Wang Chuan female Shanxi 1978-02-27 3 Liu Xueying female Guizhou 1990-11-02

同样，我们首先建表：

CREATE TABLE `tb2` (    id int(11) PRIMARY KEY auto_increment,    name varchar(255) not null,    property varchar(255),    value varchar(255)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据后如图所示：

sql语句如下：

select name as 'name',max(case property when 'gender' then value else 0 end) gender ,max(case property when 'origin_province' then value else 0 end) origin_province,max(case property when 'birthday' then value else 0 end) birthdayfrom tb2group by name;

Et voilà, le resultat est comme ci-dessous: