oracle复杂分组查询语句,oracle复杂查询是sql

一、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

oracle复杂查询是sql

标签：strong   --   type   over   复杂   连续   order   sel   必须

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉 本文系统来源：http://www.cnblogs.com/bigmonkeys/p/7896323.html