oracle 分析函数dense_rank()

最新推荐文章于 2024-03-05 12:02:50 发布

fightingJava2020

最新推荐文章于 2024-03-05 12:02:50 发布

阅读量350

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/nihousheng/article/details/42091861

oracle 专栏收录该内容

56 篇文章

订阅专栏

本文介绍如何使用SQL进行复杂的排名查询，包括如何获取特定条件下的峰值数据、如何查询成绩排名第三的学生信息等。通过具体实例展示了dense_rank()和row_number()等窗口函数的应用。

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

--本网发送秒峰值
SELECT *
FROM (SELECT monthday,
               tc,
               ct,
               dense_rank() over(PARTITION BY monthday ORDER BY ct DESC) orderid
          FROM (SELECT t.monthday,
                       to_char(t.insertime, 'yyyy-mm-dd hh24:mi:ss') tc,
                       COUNT(1) ct
                  FROM t_dz_msa_mediasend_his t
                 WHERE t.monthday LIKE '11__'
                           AND
                 t.sendtime > trunc(SYSDATE, 'yy')
                 AND t.sendno LIKE '010%'
                 GROUP BY t.monthday,
                          to_char(t.insertime, 'yyyy-mm-dd hh24:mi:ss')
                 ORDER BY ct DESC))
           WHERE ORDERid <=1

按业务按日期，分钟峰值
SELECT *
FROM (SELECT monthday,
                serviceid,
               tc,
               ct,
               dense_rank() over(PARTITION BY monthday,serviceid ORDER BY ct DESC) orderid
          FROM (SELECT t.monthday,t.serviceid,
                       to_char(t.insertime, 'yyyy-mm-dd hh24:mi') tc,
                       COUNT(1) ct
                  FROM t_dz_msa_mediasend_his t
                 WHERE t.monthday = '0109'
                           AND
                 t.sendtime > trunc(SYSDATE, 'yy')
                 GROUP BY t.monthday,t.serviceid,
                          to_char(t.insertime, 'yyyy-mm-dd hh24:mi')
                 ORDER BY ct DESC))
           WHERE ORDERid <=1

查询出学生中成绩第三的所有人信息
select *
from t_student a
where exists (select id from (select id, dense_rank() over(order by sum(score) desc) rnum
                  from t_student t
                 group by id)
             where rnum = 3
           and a.id = id)


select *
from t_student
where score = (select score
                  from (select tt.*, rownum rn
                          from (select distinct (t.score)
                                  from t_student t
                                 order by to_number(t.score) desc) tt)
                 where rn = 3)