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 PageInfoAtsAgingItemRecordVo> getAgingItemList(AtsItemRecordQo qo) {
//1、獲取工模老化字段模板
LambdaQueryWrapperAtsItemFieldPo> queryWrapper = Wrappers.lambdaQuery();
queryWrapper.eq(AtsItemFieldPo::getItemCode, AtsItemCodeConstant.GONGMO_AGING.getCode());
ListAtsItemFieldPo> fieldPoList = atsItemFieldDao.selectList(queryWrapper);
//2、組裝查詢條件
ListString> 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());
ListMapString, Object>> dataList = atsItemRecordDao.selectItemRecordListByCondition(qo);
PageInfo pageInfo = new PageInfo(dataList);
//5、組裝返回結果
ListAtsAgingItemRecordVo> recordVoList = null;
if (!CollectionUtils.isEmpty(dataList)) {
recordVoList = JSONUtils.copy(dataList, AtsAgingItemRecordVo.class);
}
pageInfo.setList(recordVoList);
return pageInfo;
}
優化后的Dao層，代碼如下：
public interface AtsItemRecordDao extends BaseMapperAtsItemRecordPo> {
ListMapString, 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 表），如下所示：
| 字段名 |
類型 |
長度 |
注釋 |
| id |
bigint |
20 |
主鍵ID |
| field_code |
varchar |
32 |
字段編碼 |
| field_name |
varchar |
32 |
字段名稱 |
| remark |
varchar |
512 |
備注 |
| created_by |
bigint |
20 |
創建人ID |
| created_time |
datetime |
0 |
創建時間 |
| updated_by |
bigint |
20 |
更新人ID |
| updated_time |
datetime |
0 |
更新時間 |
記錄表結構（ats_item_record 表），如下所示：
| 字段名 |
類型 |
長度 |
注釋 |
| id |
bigint |
20 |
主鍵ID |
| did |
varchar |
64 |
設備唯一ID |
| device_sn |
varchar |
32 |
設備sn |
| mac_address |
varchar |
32 |
設備Mac地址 |
| field_code |
varchar |
32 |
字段編碼 |
| original_record_id |
varchar |
64 |
原始記錄ID |
| record_value |
varchar |
32 |
記錄值 |
| created_by |
bigint |
20 |
創建人ID |
| created_time |
datetime |
0 |
創建時間 |
| updated_by |
bigint |
20 |
更新人ID |
| updated_time |
datetime |
0 |
更新時間 |
注：original_record_id 是縱轉橫表后，每條記錄的唯一ID，可以看做我們普通橫表的主鍵ID一樣的東西
到此 Mysql 縱表轉換為橫表介紹完成。
總結
到此這篇關于Mysql縱表轉換為橫表的文章就介紹到這了,更多相關Mysql縱表轉換為橫表內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- 詳細聊聊MySQL中慢SQL優化的方向
- 淺談MySQL之select優化方案
- MySQL千萬級數據表的優化實戰記錄
- MySql子查詢IN的執行和優化的實現
- 帶你快速搞定Mysql優化
- mysql 數據插入優化方法之concurrent_insert
- mysql優化之query_cache_limit參數說明
- MySQL優化之如何寫出高質量sql語句
- mysql查詢優化之100萬條數據的一張表優化方案
- MYSQL 的10大經典優化案例場景實戰
