- <select id="StatEnterpriseStateGroupByEquipmentType"
- parameterClass="healthStatQueryBean" resultMap="equipTypeHealthStatResultMap">
- SELECT T.EQUIPMENT_TYPE_CODE,
- Y.NAME,
- VOLTAGE_RANK,
- unknown,
- normal,
- notice,
- exception,
- serious
- FROM (SELECT EQUIPMENT_TYPE_CODE,
- VOLTAGE_RANK || 'kV' VOLTAGE_RANK,
- SUM(DECODE(HGRADE_TYPE_CODE, 0, 1, 0)) unknown,
- SUM(DECODE(HGRADE_TYPE_CODE, 1, 1, 0)) normal,
- SUM(DECODE(HGRADE_TYPE_CODE, 2, 1, 0)) notice,
- SUM(DECODE(HGRADE_TYPE_CODE, 3, 1, 0)) exception,
- SUM(DECODE(HGRADE_TYPE_CODE, 4, 1, 0)) serious
- FROM (SELECT A.SUBSTATION_ID,
- A.EQUIPMENT_ID,
- A.EQUIPMENT_TYPE_CODE,
- A.VOLTAGE_RANK,
- NVL(EH.HGRADE_TYPE_CODE, 0) HGRADE_TYPE_CODE
- FROM (SELECT EH1.SUBSTATION_ID,
- EH1.EQUIPMENT_ID,
- EH1.HGRADE_TYPE_CODE
- FROM EQUIPMENT_HEALTH EH1
- WHERE EH1.GMT_EVENT =(SELECT MAX(EH2.GMT_EVENT)
- FROM EQUIPMENT_HEALTH EH2
- WHERE EH2.SUBSTATION_ID = EH1.SUBSTATION_ID AND
- EH2.EQUIPMENT_ID = EH1.EQUIPMENT_ID)) EH,
- EQUIPMENT A,
- <dynamic>
- <isNotEmpty property="bayId">
- EQUIPMENT_ON_BAY C,
- </isNotEmpty>
- </dynamic>
- SUBSTATION S,
- ENTERPRISE E
- WHERE A.SUBSTATION_ID = EH.SUBSTATION_ID AND
- A.EQUIPMENT_ID = EH.EQUIPMENT_ID
- <dynamic>
- <isNotEmpty prepend="AND" property="enterpriseId">
- E.ENTERPRISE_ID =
- #enterpriseId#
- </isNotEmpty>
- </dynamic>
- <dynamic>
- <isNotEmpty prepend="AND" property="bayId">
- A.SUBSTATION_ID =
- C.SUBSTATION_ID AND A.EQUIPMENT_ID = C.EQUIPMENT_ID AND C.BAY_ID
- =
- #bayId#
- </isNotEmpty>
- </dynamic>
- AND A.SUBSTATION_ID = S.SUBSTATION_ID
- <dynamic>
- <isNotEmpty prepend="AND" property="substationId">
- A.SUBSTATION_ID =
- #substationId#
- </isNotEmpty>
- </dynamic>
- AND E.ENTERPRISE_ID = S.ENTERPRISE_ID
- AND A.RSTAT_TYPE_CODE = 1
- <dynamic>
- <isNotEmpty property="equipmentTypeCodes">
- <iterate property="equipmentTypeCodes" open="AND A.EQUIPMENT_TYPE_CODE in("
- close=")" conjunction=",">
- #equipmentTypeCodes[]#
- </iterate>
- </isNotEmpty>
- </dynamic>
- )
- GROUP BY EQUIPMENT_TYPE_CODE, VOLTAGE_RANK) T,
- EQUIPMENT_TYPE Y
- WHERE
- T.EQUIPMENT_TYPE_CODE = Y.EQUIPMENT_TYPE_CODE ORDER BY
- T.EQUIPMENT_TYPE_CODE
- </select>
- //该片段来自于http://www.codesnippet.cn/detail/220920136061.html
来源: http://www.codesnippet.cn/detail/220920136061.html