Oracle SQL语句练习

最近在做报表，写了一些SQL,整理一下，其中涉及到Oracle很多语法的使用，方便大家参考与复习！

SELECT 
   RESULT.SITE_NAME,RESULT.MONITOR_TIME,RESULT.COD,RESULT.AD,RESULT.ZD,RESULT.RJY,RESULT.EXCEED_COUNT
FROM
(
    SELECT * FROM
     (
            SELECT 
                 R.SITE_ID,
                 B."NAME" AS SITE_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,
                 (SELECT DISTINCT UNIT FROM T_QUALITY_ALARM A WHERE A.PROJECT_ID = R.PROJECT_ID) UNIT,
                 --查询同一个站点同一个指标的最新一条记录
                 ROW_NUMBER() OVER(PARTITION BY R.SITE_ID,R.PROJECT_ID ORDER BY R.MONITOR_TIME DESC) RN,
                 --根据站点和监测时间统计超标次数
                 SUM(CASE WHEN R.IF_EXCEED = 'YES' THEN 1 ELSE 0 END) over (partition by R.SITE_ID || R.MONITOR_TIME) EXCEED_COUNT
            FROM T_QUALITY_REALTIME_RESULT R
            LEFT JOIN T_QUALITY_RIVER_INFO B
            ON B."ID" = R.SITE_ID
            LEFT JOIN PLATFORM_DICTIONARY_DATA D
            ON R.PROJECT_ID = D."VALUE" AND D.CODE = 'RIVER_PROJECT_TYPE'
            WHERE 1=1
           AND B.DATA_SOURCE = 'GPH'
             AND B.IF_SELF_MOTION = 'YES'
             AND R.PROJECT_ID IN ('w01018','w21003','w01003','w01009')
            ORDER BY R.MONITOR_TIME DESC,R.SITE_ID
        ) T 
    WHERE T.RN = 1
  --根据指定的不同指标展示拼接的项目名称、监测结果、单位（竖转横）
) PIVOT ("MAX"(PROJECT_NAME || ':' || MONITOR_RESULT || '  ' || UNIT) FOR PROJECT_ID IN ('w01018' AS COD,'w21003' AS AD,'w01003' AS ZD,'w01009' AS RJY)) RESULT
ORDER BY RESULT.MONITOR_TIME DESC,RESULT.SITE_ID

结果展示：