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里面，這個字段在縱表中同一條記錄相同、唯一，且永遠不會改變（相當于以前橫表的主鍵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)文章希望大家以后多多支持腳本之家！
相關(guān)文章
CentOs7安裝部署Sonar環(huán)境的詳細過程(JDK1.8+MySql5.7+sonarqube7.8)
這篇文章主要介紹了CentOs7安裝部署Sonar環(huán)境(JDK1.8+MySql5.7+sonarqube7.8),本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2023-06-06
這篇文章主要介紹了MySQL數(shù)據(jù)庫表約束講解，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-06-06
MySQL中REPLACE INTO和INSERT INTO的區(qū)別分析
REPLACE的運行與INSERT很相似。只有一點例外，假如表中的一個舊記錄與一個用于PRIMARY KEY或一個UNIQUE索引的新記錄具有相同的值，則在新記錄被插入之前，舊記錄被刪除。2011-07-07
mysql執(zhí)行計劃id為空（UNION關(guān)鍵字）詳解
這篇文章主要給大家介紹了關(guān)于mysql執(zhí)行計劃id為空（UNION關(guān)鍵字）的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2018-09-09
這篇文章主要為大家介紹了MySQL監(jiān)控Innodb信息工作流程,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進步,早日升職加薪2024-02-02
這篇文章主要介紹了計算機二級考試MySQL知識點，詳細介紹了常用MYSQL命令，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-08-08
這篇文章主要介紹了解決mysql 組合AND和OR帶來的問題，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-11-11
事務(wù)(Transaction)是訪問和更新數(shù)據(jù)庫的程序執(zhí)行單元;事務(wù)中可能包含一個或多個sql語句,這些語句要么都執(zhí)行,要么都不執(zhí)行,下面這篇文章主要給大家介紹了關(guān)于MySQL基礎(chǔ)教程之事務(wù)異常情況的相關(guān)資料,需要的朋友可以參考下2022-10-10
mysql optimizer_switch查詢優(yōu)化器優(yōu)化策略
查詢優(yōu)化器是一個至關(guān)重要的組件,它負責確定執(zhí)行 SQL 查詢的最有效方法,本文主要介紹了mysql optimizer_switch查詢優(yōu)化器優(yōu)化策略,感興趣的可以了解一下2024-06-06
深入解析MySQL中的Redo Log、Undo Log和Binlog
本文詳細介紹了MySQL中的RedoLog、UndoLog和Binlog的背景、業(yè)務(wù)場景、功能、底層實現(xiàn)原理以及使用措施,通過Java代碼示例展示了如何與這些日志進行交互,進一步深化了對MySQL日志系統(tǒng)的理解,理解并合理使用這些日志,可以有效地提升數(shù)據(jù)庫的性能和可靠性2024-10-10