我现在查询出来一批数据
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
解决方案 »
- oracle查询某月所有日期的sql
- oracle与VB连接
- 求mssql和orcal两条语句
- 求一个sql语句:一个班级里面每个人所考试科目中成绩最高和最低的姓名、科目、成绩
- 谁有oracle streams的详细文档?
- 如何删除statspack所使用的表空间?
- 触发器问题 insert的when语句单独可以执行 但是放入触发器中就报错 高手指点一下
- 如何整个数据库进行EXP,IMP? 当我用FULL=Y时会出现User did not exist
- 关于client端与server端的连接问题
- sql命令,在oracle 8.0.5 中可以正常使用,但在oracle 7.3 中报错,是何缘故?
- 存储空间的问题create table TEST_BIG
- 请问一下oracle内部存储容量的问题
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
级别表中数据
1001
10010101
1002
100201
10020101
2001
200101
200102
3001
300101
30010101
问题已经解决,方法贴出来大家参考下 inner join 直接过滤掉数据表和级别表中不相关的数据
select A,sum(b)
from
(SELECT a,b from 级别表 inner join 数据表 on 级别表.a=substr(数据表.a,0,length(数据表.a)) A
with t as (
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