我现在查询出来一批数据
a b
1001 400
1002 500
200101 600
200102 700
30010101 800
如何查询才能够得到如下列结果的数据呢? 条件就是a的4位为一级,当数据有上级时,添加纪录,数据b为所有下级目录的和
a b
1001 400
1002 500
2001 1300
200101 600
200102 700
3001 800
300101 800
30010101 800
a b
1001 400
1002 500
200101 600
200102 700
30010101 800
如何查询才能够得到如下列结果的数据呢? 条件就是a的4位为一级,当数据有上级时,添加纪录,数据b为所有下级目录的和
a b
1001 400
1002 500
2001 1300
200101 600
200102 700
3001 800
300101 800
30010101 800
解决方案 »
- plsql 中(+)如何使用
- oracle 11g EM 配置出错
- sybase,正在运行系统的数据库扩容会不会发生意外
- 【求助】:oracle 监听启动失败,TNS-01189: The listener could not authenticate the user
- oracle sqlloader 导入数据问题
- Oracle逻辑结构理解问题
- oracle存储过程能输出空值参数吗?
- 字符集 数据导出乱码问题
- ●为什么这里不能用COMMIT?(CURSOR...FOR UPDATE...的循环中)
- PL/SQL过程中,可以调用VB写的ActiveX DLL 吗?
- 高手帮忙写一个触发器啊:实现,当插入数据时就动态生成一个Job,这个Job的启动时间就是插入的时间
- 想问几条语句的功能,见帖子程序段
insert into t values('1001' ,400)
insert into t values('1002' ,500)
insert into t values('200101' ,600)
insert into t values('200102' ,700)
insert into t values('30010101',800)
goselect
a,sum(b) as b
from
( select a,b from t
union all select substr(a,1,4) as a,b from t where length(a)>4
union all select substr(a,1,6) as a,b from t where length(a)>6)
group by
a
--生成测试数据
create table t(a varchar2(8),b number)
insert into t values('1001' ,400)
insert into t values('1002' ,500)
insert into t values('200101' ,600)
insert into t values('200102' ,700)
insert into t values('30010101',800) --执行查询处理
select
a,sum(b) as b
from
( select a,b from t
union all select substr(a,1,4) as a,b from t where length(a)>4
union all select substr(a,1,6) as a,b from t where length(a)>6)
group by
a--输出执行结果
/*
A B
------------ ----------
1001 400
1002 500
2001 1300
200101 600
200102 700
3001 800
300101 800
30010101 800
*/--删除测试数据
drop table t
select * from tb
union
select substr(m.a,1,4) a , sum(m.b) b from tb m , tb n where length(m.a) = 4 and length(n.a) = 6 and substr(m.a,1,4) = substr(n.a,1,4) group by substr(a,1,4)
union
select substr(m.a,1,6) a , sum(m.b) b from tb m , tb n where length(m.a) = 6 and length(n.a) = 8 and substr(m.a,1,6) = substr(n.a,1,6) group by substr(a,1,6)
order by a
select * from tb
union
select substr(m.a,1,4) a , sum(m.b) b from tb m , tb n where length(m.a) = 4 and length(n.a) = 6 and substr(m.a,1,4) = substr(n.a,1,4) group by substr(m.a,1,4)
union
select substr(m.a,1,6) a , sum(m.b) b from tb m , tb n where length(m.a) = 6 and length(n.a) = 8 and substr(m.a,1,6) = substr(n.a,1,6) group by substr(m.a,1,6)
order by a
select '1001' AS a ,400 AS b from dual UNION
select '1002' ,500 from dual union
select '200101' ,600 from dual union
select '200102' ,700 from dual union
select '30010101' AS a,800 AS b from dual
)SELECT a,SUM(b) FROM (
SELECT a,b FROM t UNION ALL
select DISTINCT substr(a,1,length(a)-2*level),b from t WHERE length(a)<>4
connect by length(a)-4-2*LEVEL>=0 )
GROUP BY a
union all不去除重复