oracle复杂查询是sql

数据库技术文章/教程1年前 (2023)发布勤奋小助

0 0

一、over()分析函数

　　分组查前几条：select * from test t where (select count(*) from test a where t.type=a.type and t.scope>a.scope)<2；

　　–rank()/dense_rank() over(partition by …order by …)
　　select * from(select t.*,rank() over(partition by t.type order by t.scope ) a from TEST t) a where a.a<3

–dense_rank()分级连续排序
select t.*,dense_rank() over(partition by t.type order by t.scope)a from test t
–rank()分级跳跃排序
select t.*,rank() over(partition by t.type order by t.scope)a from test t

select * from Test t where 2>(select count(*) from Test a where t.type=a.type and t.scope>a.scope)
select t.* from Test t,(select a.type,max(a.scope) scope from TEST a group by a.type) d where t.type=d.type and t.scope=d.scope

–笛卡尔乘积
select * from Test t,Test a
select t.* from Test t,(select a.type,max(a.scope) maxscope,min(a.scope) minscope from TEST a group by a.type) d where t.type=d.type and t.scope=d.scope

　　–
select t.*,d.maxscope-t.scope maxscope,t.scope-d.minscope minscope
from Test t,
       (select a.type, max(a.scope) maxscope, min(a.scope) minscope
          from TEST a
         group by a.type) d
where t.type = d.type

–min()/max() over(partition by …)
select t.*,
nvl(max(t.scope) over(partition by t.type), 0) – t.scope maxscope,
t.scope – nvl(min(t.scope) over(partition by t.type), 0) minscope
from test t

–lead()/lag() over(partition by … order by …)
select t.*,lead(t.scope,1,0)over(partition by t.type order by t.scope) a–同组后一个
from test t
select t.*,lag(t.scope,1,0)over(partition by t.type order by t.scope) a–同组前一个
from test t

select t.*,
       first_value(t.scope) over(partition by t.type) first_sal,
       last_value(t.scope) over(partition by t.type) last_sal,
       sum(t.scope) over(partition by t.type) sum_sal,
       avg(t.scope) over(partition by t.type) avg_sal,
       count(t.scope) over(partition by t.type) count_num,
       row_number() over(partition by t.type order by t.scope) row_num
from test t

–注：带order by子句的方法说明在使用该方法的时候必须要带order by

原文链接：https://www.cnblogs.com/bigmonkeys/p/7896323.html
本文来源互联网收集，文章内容系作者个人观点，不代表本站对观点赞同或支持。如需转载，请注明文章来源,如您发现有涉嫌抄袭侵权的内容，请联系本站核实处理。