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 tb1
union
select id as id, name as name, 'origin_province' as property, origin_province as value from tb1
union
select id as id, name as name, 'birthday' as property, birthday as value from tb1
order 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) birthday
from tb2
group by name;

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