1、 大理片sql优化

zhoushihao

2025-10-27 3c8303c418ba1e592aa7ea4a4a63f5027dd2189c

1、 大理片sql优化

 hangzhoumesParent/moduleService/CacheVerticalGlassModule/src/main/java/com/mes/bigstorage/service/impl/BigStorageCageDetailsServiceImpl.java

@@ -283,7 +283,7 @@
                .eq(BigStorageCage::getEnableState, Const.SLOT_ON)
                .eq(BigStorageCage::getRemainWidth, slotWidth).orderByAsc(BigStorageCage::getSlot)
                .inSql(BigStorageCage::getDeviceId,
                        "select distinct device_id from big_storage_cage_details where engineer_id = '" + EngineerId + "'")
                        "select distinct device_id from big_storage_cage_details where engineer_id = '" + EngineerId + "' and device_id not in (5,6)")
                .last("limit 1"));
        if (null != bigStorageCage) {
            log.info("根据版图id找到笼子内的目标格子：{},玻璃id：{}", bigStorageCage.getSlot(), glassId);

 hangzhoumesParent/moduleService/CacheVerticalGlassModule/src/main/java/com/mes/bigstorage/service/impl/BigStorageGlassInfoServiceImpl.java

@@ -136,10 +136,7 @@
            return;
        }
        Integer slotWidth = sysConfigService.queryConfigValue(ConstSysConfig.VERTICAL_SLOT_WIDTH);
//        Integer glassGap = sysConfigService.queryConfigValue(ConstSysConfig.VERTICAL_GLASS_GAP);
        Integer outCarMaxSize = sysConfigService.queryConfigValue(ConstSysConfig.VERTICAL_OUT_CAR_SIZE);
//        Engineering engineering = engineeringMapper.selectOne(new LambdaQueryWrapper<Engineering>()
//                .eq(Engineering::getEngineerId, glassInfo.getEngineerId()));
        //获取玻璃的工程id，按照工程id获取工程下的所有玻璃信息
        List<GlassInfo> glassInfoList = glassInfoService.list(new LambdaQueryWrapper<GlassInfo>()
                .eq(GlassInfo::getEngineerId, engineerId)

 hangzhoumesParent/moduleService/CacheVerticalGlassModule/src/main/resources/mapper/BigStorageCageMapper.xml

@@ -15,71 +15,72 @@
    <select id="queryFreeDeviceByUsed" resultType="java.lang.Integer">
        SELECT T.DEVICE_ID
        FROM BIG_STORAGE_CAGE T
                 LEFT JOIN (SELECT * FROM BIG_STORAGE_CAGE_DETAILS WHERE STATE NOT IN (101, 8, 9)) T1
                           ON T.SLOT = T1.SLOT
        LEFT JOIN (SELECT * FROM BIG_STORAGE_CAGE_DETAILS WHERE STATE NOT IN (101, 8, 9)) T1
        ON T.SLOT = T1.SLOT
        WHERE T.MAX_THICKNESS &gt;= #{thickness}
          and t.MIN_THICKNESS &lt;= #{thickness}
          AND T.ENABLE_STATE = 1
        and t.MIN_THICKNESS &lt;= #{thickness}
        AND T.ENABLE_STATE = 1
        and t.device_id not in (5,6)
        GROUP BY T.DEVICE_ID
        HAVING count(t.slot) - COUNT(DISTINCT T1.slot) > 0
        ORDER BY COUNT(DISTINCT T1.ENGINEER_ID), count(t.slot) - COUNT(DISTINCT T1.slot) desc
    </select>

    <select id="selectBigStorageSummary" resultType="com.mes.bigstorage.entity.dto.BigStorageSummaryDTO">
        SELECT engineer_id                              as engineerId,
               count(tempering_layout_id)               AS countTemp,
               sum(countGlass)                          AS countGlass,
               round(sum(area), 2)                      as area,
               sum(fullTemp)                            AS fullTemp,
               sum(countSlot)                           AS countSlot,
               sum(countGlass) / sum(countGlass1) * 100 AS percent,
               thickness
        SELECT engineer_id as engineerId,
        count(tempering_layout_id) AS countTemp,
        sum(countGlass) AS countGlass,
        round(sum(area), 2) as area,
        sum(fullTemp) AS fullTemp,
        sum(countSlot) AS countSlot,
        sum(countGlass) / sum(countGlass1) * 100 AS percent,
        thickness
        FROM (
                 SELECT bscd.engineer_id,
                        bscd.tempering_layout_id,
                        count(*)                                AS countGlass,
                        sum(bscd.width * bscd.height / 1000000) AS area,
                        IF
                            (gi.countGlass1 = count(*), 1, 0)   AS fullTemp,
                        count(DISTINCT bscd.slot)               AS countSlot,
                        countGlass1,
                        gi.thickness
                 FROM big_storage_cage_details bscd
                          INNER JOIN (SELECT engineer_id, tempering_layout_id, count(*) AS countGlass1, thickness
                                      FROM glass_info
                                      GROUP BY engineer_id, tempering_layout_id) gi ON bscd.engineer_id = gi.engineer_id
                     AND bscd.tempering_layout_id = gi.tempering_layout_id
                 WHERE bscd.state = 100
                 GROUP BY bscd.engineer_id,
                          bscd.tempering_layout_id
             ) AS cageSummary
        SELECT bscd.engineer_id,
        bscd.tempering_layout_id,
        count(*) AS countGlass,
        sum(bscd.width * bscd.height / 1000000) AS area,
        IF
        (gi.countGlass1 = count(*), 1, 0) AS fullTemp,
        count(DISTINCT bscd.slot) AS countSlot,
        countGlass1,
        gi.thickness
        FROM big_storage_cage_details bscd
        INNER JOIN (SELECT engineer_id, tempering_layout_id, count(*) AS countGlass1, thickness
        FROM glass_info
        GROUP BY engineer_id, tempering_layout_id) gi ON bscd.engineer_id = gi.engineer_id
        AND bscd.tempering_layout_id = gi.tempering_layout_id
        WHERE bscd.state = 100
        GROUP BY bscd.engineer_id,
        bscd.tempering_layout_id
        ) AS cageSummary
        GROUP BY engineer_id
        ORDER BY percent DESC,
                 countSlot DESC,
                 thickness DESC
        countSlot DESC,
        thickness DESC
    </select>
    <select id="queryPieChart" resultType="com.mes.largenscreen.entity.PieChartVO">
        SELECT round(sum(CASE WHEN e.station_cell = 5 THEN 1 ELSE 0 END), 2) as oneCompletedQuantity,
               round(sum(CASE WHEN e.station_cell = 5 THEN bscd.width * bscd.height / 1000000 ELSE 0 END),
                     2)                                                      as oneCompletedArea,
               round(sum(CASE WHEN e.station_cell = 6 THEN 1 ELSE 0 END), 2) as twoCompletedQuantity,
               round(sum(CASE WHEN e.station_cell = 6 THEN bscd.width * bscd.height / 1000000 ELSE 0 END),
                     2)                                                      as twoCompletedArea,
               round(sum(CASE WHEN e.station_cell = 5 and bscd.device_id is null THEN 1 ELSE 0 END),
                     2)                                                      as oneUncompletedQuantity,
               round(sum(CASE
                             WHEN e.station_cell = 5 and bscd.device_id is null THEN gi.width * gi.height / 1000000
                             ELSE 0 END),
                     2)                                                      as oneUncompletedArea,
               round(sum(CASE WHEN e.station_cell = 6 and bscd.device_id is null THEN 1 ELSE 0 END),
                     2)                                                      as twoUncompletedQuantity,
               round(sum(CASE
                             WHEN e.station_cell = 6 and bscd.device_id is null THEN gi.width * gi.height / 1000000
                             ELSE 0 END),
                     2)                                                      as twoUncompletedArea
        round(sum(CASE WHEN e.station_cell = 5 THEN bscd.width * bscd.height / 1000000 ELSE 0 END),
        2) as oneCompletedArea,
        round(sum(CASE WHEN e.station_cell = 6 THEN 1 ELSE 0 END), 2) as twoCompletedQuantity,
        round(sum(CASE WHEN e.station_cell = 6 THEN bscd.width * bscd.height / 1000000 ELSE 0 END),
        2) as twoCompletedArea,
        round(sum(CASE WHEN e.station_cell = 5 and bscd.device_id is null THEN 1 ELSE 0 END),
        2) as oneUncompletedQuantity,
        round(sum(CASE
        WHEN e.station_cell = 5 and bscd.device_id is null THEN gi.width * gi.height / 1000000
        ELSE 0 END),
        2) as oneUncompletedArea,
        round(sum(CASE WHEN e.station_cell = 6 and bscd.device_id is null THEN 1 ELSE 0 END),
        2) as twoUncompletedQuantity,
        round(sum(CASE
        WHEN e.station_cell = 6 and bscd.device_id is null THEN gi.width * gi.height / 1000000
        ELSE 0 END),
        2) as twoUncompletedArea
        FROM glass_info gi
                 left join big_storage_cage_details bscd ON gi.glass_id = bscd.glass_id
                 left join engineering e on gi.engineer_id = e.engineer_id
        left join big_storage_cage_details bscd ON gi.glass_id = bscd.glass_id
        left join engineering e on gi.engineer_id = e.engineer_id
        WHERE date(e.create_time) = date(now())
    </select>