Mysql纵表转换为横表的方法及优化教程

PHP小丑

在应用中为了从不同的视图去分析数据,会使用不同的方案去查询数据库,横表和纵表的相互转换就是其中一个常见的情景,这篇文章主要给大家介绍了关于Mysql纵表转换为横表的相关资料,需要的朋友可以参考下
1、纵表与横表
纵表：表中字段与字段的值采用key—value形式，即表中定义两个字段，其中一个字段里存放的是字段名称，另一个字段中存放的是这个字段名称代表的字段的值。
例如，下面这张ats_item_record表，其中field_code表示字段，后面的record_value表示这个字段的值

优缺点：
横表：表结构更加的清晰明了，关联查询的一些sql语句也更容易，方便易于后续开发人员的接手，但是如果字段不够，需要新增字段，会改动表结构。
纵表：扩展性更高，如果要增加一个字段，不需要改变表结构，但是一些关联查询会更加麻烦，也不便于维护与后续人员接手。
平常开发，尽量能用横表就不要用纵表，维护成本比较高昂，而且一些关联查询也很麻烦。
2、纵表转换为横表
（1）第一步，我们先把这些字段名以及相应字段的值从纵表中取出来

select r.original_record_id,r.did,r.device_sn,r.mac_address,r.record_time, r.updated_time updated_time,
(case r.field_code when 'accumulated_cooking_time' then r.record_value else '' end ) accumulated_cooking_time,
(case r.field_code when 'data_version' then r.record_value else '' end) data_version,
(case r.field_code when 'loop_num' then r.record_value else '' end) loop_num,
(case r.field_code when 'status' then r.record_value else '' end) status
from ats_item_record r 
where item_code = 'gongmo_aging'

结果：

通过 case 语句，成功把字段从纵表中取出，但是此时仍算不上一个横表，我们这里的original_record_id 是记录同一行数据的唯一id，我们这里可以通过这个字段把上面这四行合成一行记录。
注意：这里需要取出每一个字段，都要case一下，有多少个字段，就需要多少次case语句。因为一个case语句，遇到符合条件的when语句之后，后面的会不再执行。
（2）分组，合并相同行，生成横表

select * from (
  select r.original_record_id,
  max(r.did) did,
  max(r.device_sn) device_sn,
  max(r.mac_address) mac_address,
  max(r.record_time) record_time,
  max(r.updated_time) updated_time,
  max((case r.field_code when 'accumulated_cooking_time' then r.record_value else '' end )) accumulated_cooking_time,
  max((case r.field_code when 'data_version' then r.record_value else '' end)) data_version,
  max((case r.field_code when 'loop_num' then r.record_value else '' end)) loop_num,
  max((case r.field_code when 'status' then r.record_value else '' end)) status
  from ats_item_record r 
  where item_code = 'gongmo_aging'
  group by r.original_record_id
) m order by m.updated_time desc;

查询的结果：

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

• avg() 求平均数
  
• count() 求列的总数
  
• max() 求最大值
  
• min() 求最小值
  
• sum() 求和
  

大家注意一下，我把纵表同一条记录的公共字段 r.original_record_id 放到了group by里面，这个字段在纵表中同一条记录相同、唯一，且永远不会改变（相当于以前横表的主键id），然后把其他字段放到 max 中（因为其他字段要么是相同的，要么是取最大的就可以，要么是只有一个纵表记录有数值其他记录为空，所以这三种情况都可以直接用max），四条记录取最大的更新时间作为同一条记录的更新时间，在逻辑上也是合适的。然后我们把纵表字段 field_code 和 record_value 做了 max() 操作，因为同一条记录里面他们都是唯一存在的，不会发生同一条数据有两个相同的 field_code 记录，所以这样做 max() 也是没有任何问题的。
优化点：
最后这个sql是可以优化一下的，我们可以把模板字段（r.original_record_id,r.did,r.device_sn,r.mac_address,r.record_time 等），从专门存放模板字段表中全部取出来（同一个逻辑纵表的字段全部取出），然后再代码里面拼接好我们的 max() 部分，作为参数拼接进去执行，这样可以做到通用，每次如果新增加模板字段，我们不需要更改这个sql语句了（中国移动他们存放手机的参数数据就是这么干的）。
优化后的业务层（组装 sql 模板的代码），代码如下：

@override
public pageinfo<atsagingitemrecordvo> getagingitemlist(atsitemrecordqo qo) {
  //1、获取工模老化字段模板
  lambdaquerywrapper<atsitemfieldpo> querywrapper = wrappers.lambdaquery();
  querywrapper.eq(atsitemfieldpo::getitemcode, atsitemcodeconstant.gongmo_aging.getcode());
  list<atsitemfieldpo> fieldpolist = atsitemfielddao.selectlist(querywrapper);
  //2、组装查询条件
  list<string> tpllist = lists.newarraylist(), conditionlist = lists.newarraylist(), validlist = lists.newarraylist();
  if (!collectionutils.isempty(fieldpolist)) {
    //3、组装动态max查询字段
    for (atsitemfieldpo itemfieldpo : fieldpolist) {
      tpllist.add("max((case r.field_code when '" + itemfieldpo.getfieldcode() + "' then r.record_value else '' end )) " + itemfieldpo.getfieldcode());
      validlist.add(itemfieldpo.getfieldcode());
    }
    qo.settpllist(tpllist);
    //4、组装动态where查询条件
    if (stringutils.isnotblank(qo.getdid())) {
      conditionlist.add("and did like concat('%'," + qo.getdid() + ",'%')");
    }
    if (validlist.contains("batch_code") && stringutils.isnotblank(qo.getbatchcode())) {
      conditionlist.add("and batch_code like concat('%'," + qo.getbatchcode() + ",'%')");
    }
    qo.setconditionlist(conditionlist);
  }
  qo.setitemcode(atsitemcodeconstant.gongmo_aging.getcode());
  //4、获取老化自动化测试项记录
  pagehelper.startpage(qo.getpageno(), qo.getpagesize());
  list<map<string, object>> datalist = atsitemrecorddao.selectitemrecordlistbycondition(qo);
  pageinfo pageinfo = new pageinfo(datalist);
  //5、组装返回结果
  list<atsagingitemrecordvo> recordvolist = null;
  if (!collectionutils.isempty(datalist)) {
    recordvolist = jsonutils.copy(datalist, atsagingitemrecordvo.class);
  }
  pageinfo.setlist(recordvolist);
  return pageinfo;
}

优化后的dao层，代码如下：

public interface atsitemrecorddao extends basemapper<atsitemrecordpo> {
 
  list<map<string, object>> selectitemrecordlistbycondition(atsitemrecordqo qo);
}

优化后的sql语句，代码如下：

<select id="selectitemrecordlistbycondition" resulttype="java.util.hashmap"
    parametertype="com.galanz.iot.ops.restapi.model.qo.atsitemrecordqo">
  select * from (
    select r.original_record_id id,
    max(r.did) did,
    max(r.device_sn) device_sn,
    max(r.updated_time) updated_time,
    max(r.record_time) record_time,
    <if test="tpllist != null and tpllist.size() > 0">
      <foreach collection="tpllist" item="tpl" index="index" separator=",">
        ${tpl}
      </foreach>
    </if>
    from ats_item_record r
    where item_code = #{itemcode}
    group by r.original_record_id
  ) m
  <where>
    <if test="conditionlist != null and conditionlist.size() > 0">
      <foreach collection="conditionlist" item="condition" index="index">
        ${condition}
      </foreach>
    </if>
  </where>
  order by m.updated_time desc
</select>

模板字段表结构（ats_item_field 表），如下所示：
字段名类型长度注释idbigint20主键idfield_codevarchar32字段编码field_namevarchar32字段名称remarkvarchar512备注created_bybigint20创建人idcreated_timedatetime0创建时间updated_bybigint20更新人idupdated_timedatetime0更新时间记录表结构（ats_item_record 表），如下所示：
字段名类型长度注释idbigint20主键iddidvarchar64设备唯一iddevice_snvarchar32设备snmac_addressvarchar32设备mac地址field_codevarchar32字段编码original_record_idvarchar64原始记录idrecord_valuevarchar32记录值created_bybigint20创建人idcreated_timedatetime0创建时间updated_bybigint20更新人idupdated_timedatetime0更新时间注：original_record_id 是纵转横表后，每条记录的唯一id，可以看做我们普通横表的主键id一样的东西
到此 mysql 纵表转换为横表介绍完成。
总结
到此这篇关于mysql纵表转换为横表的文章就介绍到这了,更多相关mysql纵表转换为横表内容请搜索CodeAE代码之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持CodeAE代码之家！
原文链接：https://blog.csdn.net/qq_19734597/article/details/119325030