设备监控设备状态sql优化

Lius

2024-04-15 567d0c8dd0fcd4496066b1053a7652141b98e0d2

设备监控设备状态sql优化

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/xml/MdcEquipmentMapper.xml

@@ -91,51 +91,22 @@
    <!--查询设备监控信息-->
    <select id="checkStatusFromEquipmentIds" resultType="org.jeecg.modules.mdc.entity.MdcEquipmentMonitor">
        SELECT
            k.EquipmentID,
            k.CollectTime,
            k.equipmentName,
            MAX ( l.Oporation ) Oporation,
            k.id,
            k.equipment_status,
            k.equipment_type equipmentType,
            k.equipment_type_pictures
            t1.equipment_id,
            t2.CollectTime,
            t1.equipment_name,
            t2.Oporation,
            t1.id,
            t1.equipment_status,
            t1.equipment_type equipmentType,
            t3.equipment_type_pictures
        FROM
            (
            SELECT
                t.EquipmentID,
                MAX ( t.equipmentName ) equipmentName,
                MAX ( m.CollectTime ) CollectTime,
                id,
                equipment_status,
                equipment_type,
                equipment_type_pictures
            FROM
                ( SELECT equipment_id EquipmentID, equipment_Name equipmentName, id, equipment_type, equipment_status FROM mdc_equipment WHERE equipment_id IN
                    <foreach collection="equipmentIds" index="index" item="id" open="(" separator="," close=")">
                        #{id}
                    </foreach>
                 ) t
                LEFT JOIN ( SELECT equipment_type_name, equipment_type_pictures FROM mdc_equipment_type) met ON t.equipment_type = met.equipment_type_name
                LEFT JOIN ( SELECT EquipmentID, CollectTime, Oporation FROM EquipmentLog WHERE Oporation <![CDATA[ <> ]]> 23 ) m ON t.EquipmentID= m.EquipmentID
            GROUP BY
                t.EquipmentID,
                id,
                equipment_type,
                equipment_status,
                equipment_type_pictures
            ) k
            LEFT JOIN ( SELECT EquipmentID, CollectTime, Oporation FROM EquipmentLog WHERE Oporation <![CDATA[ <> ]]> 23 ) l ON k.CollectTime= l.CollectTime
            AND k.EquipmentID = l.EquipmentID
        GROUP BY
            k.EquipmentID,
            k.CollectTime,
            k.equipmentName,
            k.id,
            k.equipment_status,
            k.equipment_type,
            k.equipment_type_pictures
        ORDER BY
            EquipmentID
            mdc_equipment t1
            LEFT JOIN Equipment t2 ON t1.equipment_id = t2.EquipmentID
            LEFT JOIN mdc_equipment_type t3 ON t1.equipment_type = t3.equipment_type_name
        WHERE equipment_id IN
        <foreach collection="equipmentIds" index="index" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </select>

    <!--根据部门id查询设备id集合-->