Oracle SQL練習之Sum,Count,Decode,Case…When的使用

最近在做報表，寫了一些SQL,整理一下，其中涉及到Oracle很多文法的使用，友善大家參考與複習！

SELECT      RESULT.SITE_ID,RESULT.SITE_NAME,RESULT.STATION_TYPE,RESULT.STATION_NAME,RESULT.MONITOR_TIME,RESULT.PROJECT_ID,RESULT.PROJECT_NAME,RESULT.MONITOR_RESULT,RESULT.IF_GUARD_LEVEL,

        --根據水情站點類型和監測名額統計站點數目

        COUNT(1) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) TOTAL_COUNT,

        --根據水情站點類型和監測名額統計正常站點數目

        --SUM(CASE WHEN RESULT.IF_GUARD_LEVEL = 'YES' THEN 1 ELSE 0 END) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) NORMAL_COUNT,

        SUM(DECODE(RESULT.IF_GUARD_LEVEL,'YES',1,0)） over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) NORMAL_COUNT,

        --根據水情站點類型和監測名額統計異常站點數目

        --SUM(CASE WHEN RESULT.IF_GUARD_LEVEL = 'YES' THEN 0 ELSE 1 END) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) UNNORMAL_COUNT

        SUM(DECODE(RESULT.IF_GUARD_LEVEL,'YES',0,1)） over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) UNNORMAL_COUNT

    FROM

    (

    --查詢每個站點最新一條實時監控資料

        SELECT * FROM

         (

                SELECT 

                     R.SITE_ID,

                     B."NAME" AS SITE_NAME,

                     B.STATION_TYPE,

                     DICT."NAME" AS STATION_NAME,

                     TO_CHAR(R.MONITOR_TIME,'yyyy-MM-dd HH:mm') AS MONITOR_TIME,

                     R.PROJECT_ID,

                     D."NAME" AS PROJECT_NAME,

                     R.MONITOR_RESULT,

                     R.IF_GUARD_LEVEL,

                     --查詢同一個站點同一個名額的最新一條記錄

                     ROW_NUMBER() OVER(PARTITION BY R.SITE_ID,R.PROJECT_ID ORDER BY R.MONITOR_TIME DESC) RN

                FROM T_WATER_REALTIME_RESULT R

                LEFT JOIN V_WATER_SITE_BASE_INFO B ON B."ID" = R.SITE_ID

                LEFT JOIN PLATFORM_DICTIONARY_DATA DICT ON DICT."VALUE" = B.STATION_TYPE AND DICT.CODE = 'WATER_TYPE'

                LEFT JOIN PLATFORM_DICTIONARY_DATA D ON R.PROJECT_ID = D."VALUE" AND D.CODE = 'WATER_PROJECT_TYPE'

                WHERE 1=1

                 AND B.IF_NEW = 'YES'

                ORDER BY R.MONITOR_TIME DESC,R.SITE_ID

            ) T 

        WHERE T.RN = 1

    ) RESULT 

ORDER BY RESULT.STATION_TYPE,RESULT.MONITOR_TIME DESC