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

末蓝、 2022-05-16 01:07 620阅读 0赞

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

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

例如，下面这张project\_audit\_log表，其中date\_type字段表示为什么时间类型，后面的date\_value表示这个时间的值

![70][]

横表：所有的字段都在表结构中定义出来。如果把上面表的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;

结果：

![70 1][]

采用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;

![70 2][]

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

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

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

查询的结果：

![70 3][]

（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';

![70 4][]

（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;

![70 5][]

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

[70]: /images/20220516/d4c329e09896414fa2d0ee1022547caf.png
[70 1]: /images/20220516/23edbf8d0d164a62a160b761a964d41a.png
[70 2]: /images/20220516/7f8d75678b474650a18476afe3736e7e.png
[70 3]: /images/20220516/73da212ed2a645d0b0eb0cce6c57ca59.png
[70 4]: /images/20220516/760a7419f3ec4694875bb8cb4661905c.png
[70 5]: /images/20220516/682bf4264a914d63a29643f921153412.png