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

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'

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;

@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、組裝動態(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());
    ListMapString, Object>> dataList = atsItemRecordDao.selectItemRecordListByCondition(qo);
    PageInfo pageInfo = new PageInfo(dataList);
    //5、組裝返回結(jié)果
    ListAtsAgingItemRecordVo> recordVoList = null;
    if (!CollectionUtils.isEmpty(dataList)) {
        recordVoList = JSONUtils.copy(dataList, AtsAgingItemRecordVo.class);
    }
    pageInfo.setList(recordVoList);
    return pageInfo;
}

public interface AtsItemRecordDao extends BaseMapperAtsItemRecordPo> {
 
    ListMapString, Object>> selectItemRecordListByCondition(AtsItemRecordQo qo);
}

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>