跪求一条统计SQL?
有如下表：
个人编号 区域码编码 年度 年月 类型 基数
123 5110 2011 201101 1 0
123 5110 2011 201102 1 0
123 5110 2011 201103 1 100
123 5110 2011 201104 1 100
123 5110 2011 201105 1 100
123 5110 2011 201106 1 100
123 5110 2011 201107 1 0
123 5110 2011 201108 1 100
123 5110 2011 201109 1 100
123 5110 2011 201110 1 0
123 5110 2011 201111 1 0
123 5110 2011 201112 1 0
123 3330 2011 201101 2 0
123 3330 2011 201102 2 0
123 3330 2011 201103 2 0
123 3330 2011 201104 2 0
123 3330 2011 201105 2 0
123 3330 2011 201106 2 0
123 3330 2011 201107 2 0
123 3330 2011 201108 2 0
123 3330 2011 201109 2 0
123 3330 2011 201110 2 0
123 3330 2011 201111 2 100
123 3330 2011 201112 2 100
个人编号 区域码编码 年度 开始年月 结束年月 基数和
123 5110 2011 201101 201202 0
123 5110 2011 201103 201206 400
123 5110 2011 201107 201207 0
123 5110 2011 201108 201209 200
123 3330 2011 201110 201210 0
123 3330 2011 201111 201212 200
select 123 a, 5110 b, 2011 c, 201106 d, 1 e, 100 f from dual union
select 123 a, 5110 b, 2011 c, 201107 d, 1 e, 0 f from dual union
select 123 a, 5110 b, 2011 c, 201108 d, 1 e, 100 f from dual union
select 123 a, 5110 b, 2011 c, 201109 d, 1 e, 100 f from dual union
select 123 a, 5110 b, 2011 c, 201110 d, 1 e, 0 f from dual union
select 123 a, 5110 b, 2011 c, 201111 d, 1 e, 0 f from dual union
select 123 a, 5110 b, 2011 c, 201112 d, 1 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201101 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201102 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201103 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201104 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201105 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201106 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201107 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201108 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201109 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201110 d, 2 e, 0 f from dual union
select 123 a, 3330 b, 2011 c, 201111 d, 2 e, 100 f from dual union
select 123 a, 3330 b, 2011 c, 201112 d, 2 e, 100 f from dual
)
, b as( select
a
,b
,c
,d
,e
,f
,rownum rn
from a
)
,c as(
select
a
,b
,c
,d
,e
,f
,rn
,row_number() over (partition by a,b,c,e,case when f>0 then 1 else 0 end order by d) rn1
,rn - row_number() over (partition by a,b,c,e,case when f>0 then 1 else 0 end order by d) rn2
from b
)
select
a
,b
,c
,min(d)
,max(d)
,sum(f)
from c
group by a,b,c,rn2
order by
1,2 desc ,4
;
[解决办法]
