Oracle SQL练习之Sum,Count,Decode,Case…When的使用

最新推荐文章于 2024-09-28 02:37:01 发布

原创最新推荐文章于 2024-09-28 02:37:01 发布 · 1.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #Decode #Sum #Count #Case

Oracle 专栏收录该内容

12 篇文章

订阅专栏

博主在做报表时整理了一些涉及Oracle语法的SQL代码。代码中使用了COUNT、SUM、DECODE等函数，根据水情站点类型和监测指标统计站点数目、正常站点数目和异常站点数目，还查询了每个站点最新一条实时监控数据，方便大家参考与复习。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近在做报表，写了一些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