mysql纵表转换为横表进行多表之间的关联查询

1、数据库的表可以分为两类：纵表与横表

纵表：表中字段与字段的值采用key——value形式，即表中定义两个字段，其中一个字段里存放的是字段名称，另一个字段中存放的是这个字段名称代表的字段的值。

例如，下面这张project_audit_log表，其中date_type字段表示为什么时间类型，后面的date_value表示这个时间的值

横表：所有的字段都在表结构中定义出来。如果把上面表的date_type与date_value字段转换为shelve_date,offShelve_date,

advance_finish_date这三个字段，那这就是一张横表。

优缺点：横表的表结构更加的清晰明了，关联查询的一些sql语句也更容易，方便易于后续开发人员的接手，但是如果字段不够，需要新增字段，会改动表结构。

 纵表扩展性更高，如果要增加一个字段，不需要改变表结构，但是一些关联查询会更加麻烦，也不便于维护与后续人员接手。

平常开发，尽量能用横表就不要用纵表，维护成本比较高昂，而且一些关联查询也很麻烦。

2、纵表如何转换为横表

（1）第一步，我们先把这些字段名以及相应字段的值从纵表中取出来

select b.project_id,b.version,
(case b.date_type when 'shelveDate' then b.date_value else '' end )shelveDate,
(case b.date_type when 'offShelveDate' then b.date_value else '' end)offShelveDate,
(case b.date_type when 'advanceFinishDate' then b.date_value else '' end) advanceFinishDate
from project_audit_log b;

结果：

采用case语句，成功把字段从纵表中取出，但是此时仍算不上一个横表，我们需要把相同project_id和version的行合并（这两个字段合起来是确保表中数据唯一性的）。

注意：这里需要取出每一个字段，都要case一下，有多少个字段，就需要多少次case语句。因为一个case语句，遇到符合条件的when语句之后，后面的会不再执行。

（2）分组，合并相同行，生成横表

select b.project_id,b.version,
max(case b.date_type when 'shelveDate' then b.date_value else '' end )shelveDate,
max(case b.date_type when 'offShelveDate' then b.date_value else '' end)offShelveDate,
max(case b.date_type when 'advanceFinishDate' then b.date_value else '' end) advanceFinishDate
from project_audit_log b group by b.project_id,b.version;

注意：这里采用group by 分组的时候，需要给字段加上max函数。用group by 分组的时候，一般搭配聚合函数使用，常见的聚合函数：

• AVG() 求平均数
• COUNT() 求列的总数
• MAX() 求最大值
• MIN() 求最小值
• SUM() 求和

这里不使用max聚合函数的话，会从分组里面显示第一次出现的字段值，去掉max函数之后的sql：

select b.project_id,b.version,
(case b.date_type when 'shelveDate' then b.date_value else '' end )shelveDate,
(case b.date_type when 'offShelveDate' then b.date_value else '' end)offShelveDate,
(case b.date_type when 'advanceFinishDate' then b.date_value else '' end) advanceFinishDate
from project_audit_log b group by b.project_id,b.version;

查询的结果：

（3）纵表变成横表之后，就可以很方便的进行关联查询以及关于纵表字段的条件查询，比如，另有一张表project_info ，与project_audit_log关联，并且查出shelveDate时间大于2018-08-03 16:23:59与offShelveDate小于2018-08-06 15:12:22

select a.id,a.version,a.project_name,e.shelveDate,e.offShelveDate,e.advanceFinishDate
from project_info a left join 
(select b.project_id,b.version,
max(case b.date_type when 'shelveDate' then b.date_value else '' end )shelveDate,
max(case b.date_type when 'offShelveDate' then b.date_value else '' end)offShelveDate,
max(case b.date_type when 'advanceFinishDate' then b.date_value else '' end) advanceFinishDate
from project_audit_log b group by b.project_id,b.version) e on a.id = e.project_id and a.version = e.version where e.shelveDate >= '2018-08-03 16:23:59' and e.offShelveDate <= '2018-08-06 15:12:22';

（4）采用GROUP_CONCAT，进行字段合并，合成一个横表

select a.project_id,a.version,GROUP_CONCAT(a.date_type,a.date_value) date from project_audit_log a  GROUP BY a.project_id,a.version;

然后进行与其他表的关联查询，但是这样的做法，对于纵表中的字段条件查询也不方便，如果我们要筛选出shelveDate与offShelveDate或者advanceFinishDate条件，也挺不方便的，比较适合于不对纵表字段进行条件查询，只与其它表关联查询出纵表信息。