Mysql縱表轉(zhuǎn)換為橫表的方法及優(yōu)化教程
1、縱表與橫表
縱表：表中字段與字段的值采用key—value形式，即表中定義兩個字段，其中一個字段里存放的是字段名稱，另一個字段中存放的是這個字段名稱代表的字段的值。
例如，下面這張ats_item_record表，其中field_code表示字段，后面的record_value表示這個字段的值
優(yōu)缺點：
橫表：表結(jié)構(gòu)更加的清晰明了，關(guān)聯(lián)查詢的一些sql語句也更容易，方便易于后續(xù)開發(fā)人員的接手，但是如果字段不夠，需要新增字段，會改動表結(jié)構(gòu)。
縱表：擴展性更高，如果要增加一個字段，不需要改變表結(jié)構(gòu)，但是一些關(guān)聯(lián)查詢會更加麻煩，也不便于維護與后續(xù)人員接手。
平常開發(fā)，盡量能用橫表就不要用縱表，維護成本比較高昂，而且一些關(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 語句，成功把字段從縱表中取出，但是此時仍算不上一個橫表，我們這里的original_record_id 是記錄同一行數(shù)據(jù)的唯一ID，我們這里可以通過這個字段把上面這四行合成一行記錄。
注意：這里需要取出每一個字段，都要case一下，有多少個字段，就需要多少次case語句。因為一個case語句，遇到符合條件的when語句之后，后面的會不再執(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 分組的時候，需要給字段加上max函數(shù)。用group by 分組的時候，一般搭配聚合函數(shù)使用，常見的聚合函數(shù)：
- AVG() 求平均數(shù)
- COUNT() 求列的總數(shù)
- MAX() 求最大值
- MIN() 求最小值
- SUM() 求和
大家注意一下，我把縱表同一條記錄的公共字段 r.original_record_id 放到了group by里面，這個字段在縱表中同一條記錄相同、唯一，且永遠(yuǎn)不會改變（相當(dāng)于以前橫表的主鍵ID），然后把其他字段放到 max 中（因為其他字段要么是相同的，要么是取最大的就可以，要么是只有一個縱表記錄有數(shù)值其他記錄為空，所以這三種情況都可以直接用max），四條記錄取最大的更新時間作為同一條記錄的更新時間，在邏輯上也是合適的。然后我們把縱表字段 field_code 和 record_value 做了 max() 操作，因為同一條記錄里面他們都是唯一存在的，不會發(fā)生同一條數(shù)據(jù)有兩個相同的 field_code 記錄，所以這樣做 max() 也是沒有任何問題的。
優(yōu)化點：
最后這個SQL是可以優(yōu)化一下的，我們可以把模板字段（r.original_record_id,r.did,r.device_sn,r.mac_address,r.record_time 等），從專門存放模板字段表中全部取出來（同一個邏輯縱表的字段全部取出），然后再代碼里面拼接好我們的 max() 部分，作為參數(shù)拼接進去執(zhí)行，這樣可以做到通用，每次如果新增加模板字段，我們不需要更改這個SQL語句了（中國移動他們存放手機的參數(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、組裝動態(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、組裝動態(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、獲取老化自動化測試項記錄
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)建時間 |
| updated_by | bigint | 20 | 更新人ID |
| updated_time | datetime | 0 | 更新時間 |
記錄表結(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)建時間 |
| updated_by | bigint | 20 | 更新人ID |
| updated_time | datetime | 0 | 更新時間 |
注：original_record_id 是縱轉(zhuǎn)橫表后，每條記錄的唯一ID，可以看做我們普通橫表的主鍵ID一樣的東西
到此 Mysql 縱表轉(zhuǎn)換為橫表介紹完成。
總結(jié)
到此這篇關(guān)于Mysql縱表轉(zhuǎn)換為橫表的文章就介紹到這了,更多相關(guān)Mysql縱表轉(zhuǎn)換為橫表內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信