select * from ( WITH A AS ( SELECT 'ROOT' ADDVCD,'全国' ADDVNM FROM DUAL ), B AS ( select 'ROOT' ADDVCD, count(case when FUND_DATE = '2022' then 1 end) PLAN2022, count(case when FUND_DATE = '2023' then 1 end) PLAN2023, count(case when FUND_DATE = '2024' then 1 end) PLAN2024, count(case when FUND_DATE = '2025' then 1 end) PLAN2025, count(case when FUND_DATE in ('2022','2023','2024') then 1 end) PLANSUM, count(case when FUND_DATE = '2022' and IS_YR = '1' then 1 end) ZJ2022, count(case when FUND_DATE = '2023' and IS_YR = '1' then 1 end) ZJ2023, count(case when FUND_DATE = '2024' and IS_YR = '1' then 1 end) ZJ2024, count(case when FUND_DATE = '2025' and IS_YR = '1' then 1 end) ZJ2025, count(case when FUND_DATE in ('2022','2023','2024') and IS_YR = '1' then 1 end) ZJSUM, count(case when FUND_DATE in ('2022','2023','2024','2025') and IS_YR = '1' then 1 end) ZJ, sum(case when FUND_DATE = '2022' then FUND end) FUND2022, sum(case when FUND_DATE = '2023' then FUND end) FUND2023, sum(case when FUND_DATE = '2024' then FUND end) FUND2024, sum(case when FUND_DATE = '2025' then FUND end) FUND2025, sum(case when FUND_DATE in ('2022','2023','2024') then FUND end) FUNDSUM from WI_MEAS_FUND_INVENTORY where TRANS_MODE_CODE = '1' and STATUS in ('1','2') ), C AS ( select 'ROOT' ADDVCD, SUM( case when a.FUND_DATE = '2022' then MONTH_DAY_WATER_NUMBER end) MONTH_DAY_NUMBER2022, SUM( case when a.FUND_DATE = '2023' then MONTH_DAY_WATER_NUMBER end) MONTH_DAY_NUMBER2023, SUM( case when a.FUND_DATE = '2024' then MONTH_DAY_WATER_NUMBER end) MONTH_DAY_NUMBER2024, SUM( case when a.FUND_DATE = '2025' then MONTH_DAY_WATER_NUMBER end) MONTH_DAY_NUMBER2025, SUM( case when a.FUND_DATE in ('2022','2023','2024') then MONTH_DAY_WATER_NUMBER end) MONTH_DAY_NUMBERSUM, SUM( case when a.FUND_DATE = '2022' then YEAR_DAY_WATER_NUMBER end) YEAR_DAY_NUMBER2022, SUM( case when a.FUND_DATE = '2023' then YEAR_DAY_WATER_NUMBER end) YEAR_DAY_NUMBER2023, SUM( case when a.FUND_DATE = '2024' then YEAR_DAY_WATER_NUMBER end) YEAR_DAY_NUMBER2024, SUM( case when a.FUND_DATE = '2025' then YEAR_DAY_WATER_NUMBER end) YEAR_DAY_NUMBER2025, SUM( case when a.FUND_DATE in ('2022','2023','2024') then YEAR_DAY_WATER_NUMBER end) YEAR_DAY_NUMBERSUM, SUM( case when a.FUND_DATE = '2022' then case when b.id is not null then YJ_MONTH_WATER_NUMBER else to_number(to_char(sysdate -1, 'dd')) end end) YJ_MONTH_NUMBER2022, SUM( case when a.FUND_DATE = '2023' then case when b.id is not null then YJ_MONTH_WATER_NUMBER else to_number(to_char(sysdate -1, 'dd')) end end) YJ_MONTH_NUMBER2023, SUM( case when a.FUND_DATE = '2024' then case when b.id is not null then YJ_MONTH_WATER_NUMBER else to_number(to_char(sysdate -1, 'dd')) end end) YJ_MONTH_NUMBER2024, SUM( case when a.FUND_DATE = '2025' then case when b.id is not null then YJ_MONTH_WATER_NUMBER else to_number(to_char(sysdate -1, 'dd')) end end) YJ_MONTH_NUMBER2025, SUM( case when a.FUND_DATE in ('2022','2023','2024') then case when b.id is not null then YJ_MONTH_WATER_NUMBER else to_number(to_char(sysdate -1, 'dd')) end end) YJ_MONTH_NUMBERSUM, SUM( case when a.FUND_DATE = '2022' then case when b.id is not null then YJ_YEAR_WATER_NUMBER else to_number(to_char(sysdate -1, 'ddd')) -180 end end) YJ_YEAR_NUMBER2022, SUM( case when a.FUND_DATE = '2023' then case when b.id is not null then YJ_YEAR_WATER_NUMBER else to_number(to_char(sysdate -1, 'ddd')) -180 end end) YJ_YEAR_NUMBER2023, SUM( case when a.FUND_DATE = '2024' then case when b.id is not null then YJ_YEAR_WATER_NUMBER else to_number(to_char(sysdate -1, 'ddd')) -180 end end) YJ_YEAR_NUMBER2024, SUM( case when a.FUND_DATE = '2025' then case when b.id is not null then YJ_YEAR_WATER_NUMBER else to_number(to_char(sysdate -1, 'ddd')) -180 end end) YJ_YEAR_NUMBER2025, SUM( case when a.FUND_DATE in ('2022','2023','2024') then case when b.id is not null then YJ_YEAR_WATER_NUMBER else to_number(to_char(sysdate -1, 'ddd')) -180 end end) YJ_YEAR_NUMBERSUM, COUNT( case when a.FUND_DATE = '2022' and IS_WATER_ABNORMAL_MONTH = '1' then 1 end) ABNORMAL_MONTH2022, COUNT( case when a.FUND_DATE = '2023' and IS_WATER_ABNORMAL_MONTH = '1' then 1 end) ABNORMAL_MONTH2023, COUNT( case when a.FUND_DATE = '2024' and IS_WATER_ABNORMAL_MONTH = '1' then 1 end) ABNORMAL_MONTH2024, COUNT( case when a.FUND_DATE = '2025' and IS_WATER_ABNORMAL_MONTH = '1' then 1 end) ABNORMAL_MONTH2025, COUNT( case when a.FUND_DATE in ('2022','2023','2024') and IS_WATER_ABNORMAL_MONTH = '1' then 1 end) ABNORMAL_MONTHSUM, COUNT( case when a.FUND_DATE = '2022' and IS_WATER_ABNORMAL_YEAR = '1' then 1 end) ABNORMAL_YEAR2022, COUNT( case when a.FUND_DATE = '2023' and IS_WATER_ABNORMAL_YEAR = '1' then 1 end) ABNORMAL_YEAR2023, COUNT( case when a.FUND_DATE = '2024' and IS_WATER_ABNORMAL_YEAR = '1' then 1 end) ABNORMAL_YEAR2024, COUNT( case when a.FUND_DATE = '2025' and IS_WATER_ABNORMAL_YEAR = '1' then 1 end) ABNORMAL_YEAR2025, COUNT( case when a.FUND_DATE in ('2022','2023','2024') and IS_WATER_ABNORMAL_YEAR = '1' then 1 end) ABNORMAL_YEARSUM, COUNT( case when a.FUND_DATE in ('2022','2023','2024','2025') and IS_WATER_ABNORMAL_YEAR = '1' then 1 end) ABNORMAL_YEAR from WI_MEAS_FUND_INVENTORY a left join WI_MEASURE_WATER_MANAGE_25_12 b on b.TRANS_MODE_CODE = '1' and a.meas_code = b.meas_code and ( ( a.CERT_NUM_CODE = b.CERT_NUM_CODE and a.LICENSE_WHITELIST = '0') or a.LICENSE_WHITELIST = '1' ) where a.TRANS_MODE_CODE = '1' and a.STATUS in ('1','2') ) SELECT A.ADDVCD, A.ADDVNM, '2025-12-01' DATE_STR, B.PLAN2022, B.PLAN2023, B.PLAN2024, B.PLAN2025, B.PLANSUM, B.ZJ2022, B.ZJ2023, B.ZJ2024, B.ZJ2025, B.ZJSUM, CASE WHEN B.PLAN2022 > 0 THEN ROUND(B.ZJ2022/B.PLAN2022,4)*100 ELSE 0 END ZJ2022AR, CASE WHEN B.PLAN2023 > 0 THEN ROUND(B.ZJ2023/B.PLAN2023,4)*100 ELSE 0 END ZJ2023AR, CASE WHEN B.PLAN2024 > 0 THEN ROUND(B.ZJ2024/B.PLAN2024,4)*100 ELSE 0 END ZJ2024AR, CASE WHEN B.PLAN2025 > 0 THEN ROUND(B.ZJ2025/B.PLAN2025,4)*100 ELSE 0 END ZJ2025AR, CASE WHEN B.PLANSUM > 0 THEN ROUND(B.ZJSUM/B.PLANSUM,4)*100 ELSE 0 END ZJSUMAR, B.FUND2022, B.FUND2023, B.FUND2024, B.FUND2025, B.FUNDSUM, CASE WHEN C.YJ_MONTH_NUMBER2022 >0 THEN ROUND(C.MONTH_DAY_NUMBER2022/C.YJ_MONTH_NUMBER2022,4) *100 ELSE 0 END ZJ2022YR, CASE WHEN C.YJ_MONTH_NUMBER2023 >0 THEN ROUND(C.MONTH_DAY_NUMBER2023/C.YJ_MONTH_NUMBER2023,4) *100 ELSE 0 END ZJ2023YR, CASE WHEN C.YJ_MONTH_NUMBER2024 >0 THEN ROUND(C.MONTH_DAY_NUMBER2024/C.YJ_MONTH_NUMBER2024,4) *100 ELSE 0 END ZJ2024YR, CASE WHEN C.YJ_MONTH_NUMBER2025 >0 THEN ROUND(C.MONTH_DAY_NUMBER2025/C.YJ_MONTH_NUMBER2025,4) *100 ELSE 0 END ZJ2025YR, CASE WHEN C.YJ_MONTH_NUMBERSUM >0 THEN ROUND(C.MONTH_DAY_NUMBERSUM/C.YJ_MONTH_NUMBERSUM,4) *100 ELSE 0 END ZJSUMYR, CASE WHEN C.YJ_YEAR_NUMBER2022 > 0 THEN ROUND(C.YEAR_DAY_NUMBER2022/C.YJ_YEAR_NUMBER2022,4) *100 ELSE 0 END ZJ2022YRY, CASE WHEN C.YJ_YEAR_NUMBER2023 > 0 THEN ROUND(C.YEAR_DAY_NUMBER2023/C.YJ_YEAR_NUMBER2023,4) *100 ELSE 0 END ZJ2023YRY, CASE WHEN C.YJ_YEAR_NUMBER2024 > 0 THEN ROUND(C.YEAR_DAY_NUMBER2024/C.YJ_YEAR_NUMBER2024,4) *100 ELSE 0 END ZJ2024YRY, CASE WHEN C.YJ_YEAR_NUMBER2025 > 0 THEN ROUND(C.YEAR_DAY_NUMBER2025/C.YJ_YEAR_NUMBER2025,4) *100 ELSE 0 END ZJ2025YRY, CASE WHEN C.YJ_YEAR_NUMBERSUM > 0 THEN ROUND(C.YEAR_DAY_NUMBERSUM/C.YJ_YEAR_NUMBERSUM,4) *100 ELSE 0 END ZJSUMYRY, CASE WHEN B.ZJ2022 > 0 THEN ROUND(C.ABNORMAL_MONTH2022/B.ZJ2022,4) *100 ELSE 0 END ZJ2022ERRM, CASE WHEN B.ZJ2023 > 0 THEN ROUND(C.ABNORMAL_MONTH2023/B.ZJ2023,4) *100 ELSE 0 END ZJ2023ERRM, CASE WHEN B.ZJ2024 > 0 THEN ROUND(C.ABNORMAL_MONTH2024/B.ZJ2024,4) *100 ELSE 0 END ZJ2024ERRM, CASE WHEN B.ZJ2025 > 0 THEN ROUND(C.ABNORMAL_MONTH2025/B.ZJ2025,4) *100 ELSE 0 END ZJ2025ERRM, CASE WHEN B.ZJSUM > 0 THEN ROUND(C.ABNORMAL_MONTHSUM/B.ZJSUM,4) *100 ELSE 0 END ZJSUMERRM, CASE WHEN B.ZJ2022 > 0 THEN ROUND(C.ABNORMAL_YEAR2022/B.ZJ2022,4) *100 ELSE 0 END ZJ2022ERRY, CASE WHEN B.ZJ2023 > 0 THEN ROUND(C.ABNORMAL_YEAR2023/B.ZJ2023,4) *100 ELSE 0 END ZJ2023ERRY, CASE WHEN B.ZJ2024 > 0 THEN ROUND(C.ABNORMAL_YEAR2024/B.ZJ2024,4) *100 ELSE 0 END ZJ2024ERRY, CASE WHEN B.ZJ2025 > 0 THEN ROUND(C.ABNORMAL_YEAR2025/B.ZJ2025,4) *100 ELSE 0 END ZJ2025ERRY, CASE WHEN B.ZJSUM > 0 THEN ROUND(C.ABNORMAL_YEARSUM/B.ZJSUM,4) *100 ELSE 0 END ZJSUMERRY, CASE WHEN B.ZJ > 0 THEN ROUND(C.ABNORMAL_YEAR/B.ZJ,4) *100 ELSE 0 END ZJERRY FROM A LEFT JOIN B ON A.ADDVCD = B.ADDVCD LEFT JOIN C ON A.ADDVCD = C.ADDVCD )
优化该sql
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
2981
