快捷導(dǎo)航

Mysql縱表轉(zhuǎn)換為橫表的方法及優(yōu)化教程

更新時(shí)間：2021年08月04日 11:58:23 作者：張志翔

在應(yīng)用中為了從不同的視圖去分析數(shù)據(jù),會(huì)使用不同的方案去查詢數(shù)據(jù)庫,橫表和縱表的相互轉(zhuǎn)換就是其中一個(gè)常見的情景,這篇文章主要給大家介紹了關(guān)于Mysql縱表轉(zhuǎn)換為橫表的相關(guān)資料,需要的朋友可以參考下

1、縱表與橫表

縱表：表中字段與字段的值采用key—value形式，即表中定義兩個(gè)字段，其中一個(gè)字段里存放的是字段名稱，另一個(gè)字段中存放的是這個(gè)字段名稱代表的字段的值。

例如，下面這張ats_item_record表，其中field_code表示字段，后面的record_value表示這個(gè)字段的值

優(yōu)缺點(diǎn)：

橫表：表結(jié)構(gòu)更加的清晰明了，關(guān)聯(lián)查詢的一些sql語句也更容易，方便易于后續(xù)開發(fā)人員的接手，但是如果字段不夠，需要新增字段，會(huì)改動(dòng)表結(jié)構(gòu)。

縱表：擴(kuò)展性更高，如果要增加一個(gè)字段，不需要改變表結(jié)構(gòu)，但是一些關(guān)聯(lián)查詢會(huì)更加麻煩，也不便于維護(hù)與后續(xù)人員接手。

平常開發(fā)，盡量能用橫表就不要用縱表，維護(hù)成本比較高昂，而且一些關(guān)聯(lián)查詢也很麻煩。

2、縱表轉(zhuǎn)換為橫表

（1）第一步，我們先把這些字段名以及相應(yīng)字段的值從縱表中取出來

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'

結(jié)果：

通過 case 語句，成功把字段從縱表中取出，但是此時(shí)仍算不上一個(gè)橫表，我們這里的original_record_id 是記錄同一行數(shù)據(jù)的唯一ID，我們這里可以通過這個(gè)字段把上面這四行合成一行記錄。

注意：這里需要取出每一個(gè)字段，都要case一下，有多少個(gè)字段，就需要多少次case語句。因?yàn)橐粋€(gè)case語句，遇到符合條件的when語句之后，后面的會(huì)不再執(zhí)行。

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

查詢的結(jié)果：

注意：這里采用group by 分組的時(shí)候，需要給字段加上max函數(shù)。用group by 分組的時(shí)候，一般搭配聚合函數(shù)使用，常見的聚合函數(shù)：

AVG() 求平均數(shù)
COUNT() 求列的總數(shù)
MAX() 求最大值
MIN() 求最小值
SUM() 求和

大家注意一下，我把縱表同一條記錄的公共字段 r.original_record_id 放到了group by里面，這個(gè)字段在縱表中同一條記錄相同、唯一，且永遠(yuǎn)不會(huì)改變（相當(dāng)于以前橫表的主鍵ID），然后把其他字段放到 max 中（因?yàn)槠渌侄我词窍嗤?，要么是取最大的就可以，要么是只有一個(gè)縱表記錄有數(shù)值其他記錄為空，所以這三種情況都可以直接用max），四條記錄取最大的更新時(shí)間作為同一條記錄的更新時(shí)間，在邏輯上也是合適的。然后我們把縱表字段 field_code 和 record_value 做了 max() 操作，因?yàn)橥粭l記錄里面他們都是唯一存在的，不會(huì)發(fā)生同一條數(shù)據(jù)有兩個(gè)相同的 field_code 記錄，所以這樣做 max() 也是沒有任何問題的。

優(yōu)化點(diǎn)：

最后這個(gè)SQL是可以優(yōu)化一下的，我們可以把模板字段（r.original_record_id,r.did,r.device_sn,r.mac_address,r.record_time 等），從專門存放模板字段表中全部取出來（同一個(gè)邏輯縱表的字段全部取出），然后再代碼里面拼接好我們的 max() 部分，作為參數(shù)拼接進(jìn)去執(zhí)行，這樣可以做到通用，每次如果新增加模板字段，我們不需要更改這個(gè)SQL語句了（中國移動(dòng)他們存放手機(jī)的參數(shù)數(shù)據(jù)就是這么干的）。

優(yōu)化后的業(yè)務(wù)層（組裝 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、組裝動(dòng)態(tài)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、組裝動(dòng)態(tài)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、獲取老化自動(dòng)化測試項(xiàng)記錄
    PageHelper.startPage(qo.getPageNo(), qo.getPageSize());
    List<Map<String, Object>> dataList = atsItemRecordDao.selectItemRecordListByCondition(qo);
    PageInfo pageInfo = new PageInfo(dataList);
    //5、組裝返回結(jié)果
    List<AtsAgingItemRecordVo> recordVoList = null;
    if (!CollectionUtils.isEmpty(dataList)) {
        recordVoList = JSONUtils.copy(dataList, AtsAgingItemRecordVo.class);
    }
    pageInfo.setList(recordVoList);
    return pageInfo;
}

優(yōu)化后的Dao層，代碼如下：

public interface AtsItemRecordDao extends BaseMapper<AtsItemRecordPo> {
 
    List<Map<String, Object>> selectItemRecordListByCondition(AtsItemRecordQo qo);
}

優(yōu)化后的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>

模板字段表結(jié)構(gòu)（ats_item_field 表），如下所示：

字段名	類型	長度	注釋
id	bigint	20	主鍵ID
field_code	varchar	32	字段編碼
field_name	varchar	32	字段名稱
remark	varchar	512	備注
created_by	bigint	20	創(chuàng)建人ID
created_time	datetime	0	創(chuàng)建時(shí)間
updated_by	bigint	20	更新人ID
updated_time	datetime	0	更新時(shí)間

記錄表結(jié)構(gòu)（ats_item_record 表），如下所示：

字段名	類型	長度	注釋
id	bigint	20	主鍵ID
did	varchar	64	設(shè)備唯一ID
device_sn	varchar	32	設(shè)備sn
mac_address	varchar	32	設(shè)備Mac地址
field_code	varchar	32	字段編碼
original_record_id	varchar	64	原始記錄ID
record_value	varchar	32	記錄值
created_by	bigint	20	創(chuàng)建人ID
created_time	datetime	0	創(chuàng)建時(shí)間
updated_by	bigint	20	更新人ID
updated_time	datetime	0	更新時(shí)間