mysql纵表转换为横表进行多表之间的关联查询 |
您所在的位置:网站首页 › sql查两个表的关联查询返回两列 › 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 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |