有如下表:个人编号 区域码编码 年度 年月 类型 基数
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按以上表用一条SQL语句统计出如下结果(注:个人编号、区域码编码、年月为联合主键):个人编号 区域码编码 年度 开始年月 结束年月 基数和
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跪求解题啊!谢谢各位大侠了,小弟很急呀!麻烦了,在线等哟。。
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按以上表用一条SQL语句统计出如下结果(注:个人编号、区域码编码、年月为联合主键):个人编号 区域码编码 年度 开始年月 结束年月 基数和
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跪求解题啊!谢谢各位大侠了,小弟很急呀!麻烦了,在线等哟。。
SQL
解决方案 »
- orcle备份还原问题
- 用什么技术可以实现数据库的实时更新
- SELECT * FROM 表 ;取清单时,如何在最后一行展现统计行数
- oralce如何跨域访问?
- plsql下如何导出生产库里的所有用户/表空间/dblink的创建脚本(oracle)?
- oracle10g 存储过程利用sys_refcursor返回结果集的问题?
- 请帮忙解释下这个sql语句 (rank,partition)
- oracle9update触发器技术问题
- 指定回滚段问题????????
- Oracle启动
- Oracle 数据库中 根据 【组合分类】 查询 数据 怎么写SQL ???
- 用一个表的二个字段来修改另一个表的二个字段
select 123 a, 5110 b, 2011 c, 201101 d, 1 e, 0 f from dual union
select 123 a, 5110 b, 2011 c, 201102 d, 1 e, 0 f from dual union
select 123 a, 5110 b, 2011 c, 201103 d, 1 e, 100 f from dual union
select 123 a, 5110 b, 2011 c, 201104 d, 1 e, 100 f from dual union
select 123 a, 5110 b, 2011 c, 201105 d, 1 e, 100 f from dual union
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
;