123123

最新推荐文章于 2024-10-15 15:24:52 发布

左慈L

最新推荐文章于 2024-10-15 15:24:52 发布

阅读量191

点赞数 1

文章标签：前端 javascript linux

本文链接：https://blog.csdn.net/a123lwlw/article/details/141687484

版权

WITH BaseData AS (
SELECT
a.org_no,
a.HEAT_AREA AS sum_heat_area,
MAX(CASE
WHEN b.date_ym = #{cpDate} AND b.HEAT_PROP = ‘00’ AND b.HEAT_STATUS = ‘00’
THEN b.HEAT_AREA
ELSE 0
END) AS cp_heat_area,
MAX(CASE
WHEN b.date_ym = #{dataDate} AND b.HEAT_PROP = ‘01’ AND b.HEAT_STATUS = ‘00’
THEN b.HEAT_AREA
ELSE 0
END) AS jm_heat_area,
MAX(CASE
WHEN b.date_ym = #{cpDate} AND b.HEAT_PROP = ‘01’ AND b.HEAT_STATUS = ‘00’
THEN b.HEAT_AREA
ELSE 0
END) AS jm_cp_heat_area,
MAX(CASE
WHEN b.date_ym = #{dataDate} AND b.HEAT_PROP = ‘02’ AND b.HEAT_STATUS = ‘00’
THEN b.HEAT_AREA
ELSE 0
END) AS fjm_heat_area,
MAX(CASE
WHEN b.date_ym = #{cpDate} AND b.HEAT_PROP = ‘02’ AND b.HEAT_STATUS = ‘00’
THEN b.HEAT_AREA
ELSE 0
END) AS fjm_cp_heat_area
FROM
NJ_SUP_HEAT_STAT a
LEFT JOIN
NJ_SUP_HEAT_STAT b ON a.org_no = b.org_no
AND b.date_ym IN (#{dataDate}, #{cpDate})
AND b.HEAT_STATUS = ‘00’
WHERE
a.date_ym = #{dataDate}
AND a.org_no = #{orgNo}
AND a.HEAT_PROP = ‘00’
AND a.HEAT_STATUS = ‘00’
GROUP BY
a.org_no, a.HEAT_AREA
)
SELECT
T.org_no AS orgNo,
T.sum_heat_area AS sumHeatArea,
T.cp_heat_area AS cpHeatArea,
T.jm_heat_area AS jmHeatArea,
T.jm_cp_heat_area AS jmCpHeatArea,
T.fjm_heat_area AS fjmHeatArea,
T.fjm_cp_heat_area AS fjmCpHeatArea,
ROUND(CASE
WHEN T.cp_heat_area = 0 THEN 0
ELSE (T.sum_heat_area - T.cp_heat_area) / T.cp_heat_area
END, 4) AS sumAreaTb,
ROUND(CASE
WHEN T.jm_cp_heat_area = 0 THEN 0
ELSE (T.jm_heat_area - T.jm_cp_heat_area) / T.jm_cp_heat_area
END, 4) AS jmAreaTb,
ROUND(CASE
WHEN T.fjm_cp_heat_area = 0 THEN 0
ELSE (T.fjm_heat_area - T.fjm_cp_heat_area) / T.fjm_cp_heat_area
END, 4) AS fjmAreaTb,
ROUND(CASE
WHEN T.sum_heat_area = 0 THEN 1
ELSE T.jm_heat_area / T.sum_heat_area
END, 4) * 100 || ‘%’ AS jmAreaZb,
ROUND(CASE
WHEN T.sum_heat_area = 0 THEN 1
ELSE T.fjm_heat_area / T.sum_heat_area
END, 4) * 100 || ‘%’ AS fjmAreaTb
FROM
BaseData T;