表a
ID 父ID 数量
1 0 1
2 1 2
3 2 3
4 0 4
5 4 5
6 4 6
结果视图
ID 数量
1 6
2 5
3 3
4 10
5 5
6 6
这个试图该怎么建立啊求教!
ID 父ID 数量
1 0 1
2 1 2
3 2 3
4 0 4
5 4 5
6 4 6
结果视图
ID 数量
1 6
2 5
3 3
4 10
5 5
6 6
这个试图该怎么建立啊求教!
解决方案 »
- 求优化SQL,提高执行速度
- 请大家帮一下!Oracle联合查询的问题!
- 请问如何通过jdbc得到oracle的rowid
- oracle9i的一个session要占多少内存,怎么我的HP下的oracle9i经常出现内存不够的现象,来者有分。
- oracle如何在触发器中调用有参数的存储过程?
- 要从一个表中随机取十条记录和取前十条记录应该怎么取啊?
- 关于 solaris 下 oracle9ias 的安装问题
- 关于JAVA ,JSP, 和Oracle 之间的问题
- 请问这个sql查询该怎么实现?
- 求大神指教,自己被绕晕了,关于sqlServer转oracle的
- 请教一个关于模糊查询来过滤数据的sql语句
- 求助: Oralce Clob带符号的字符匹配
ID 父ID 数量
1 0 1
2 1 2
3 2 3
4 0 4
5 4 5
6 4 6
结果视图
ID 数量
1 6
2 5
3 3
4 15
5 5
6 6
这个试图该怎么建立啊求教!
结果视图就是将该ID及其所有子ID的数量相加得出的数量。
create or replace view v_test
as
select id,
(select count(1) from test where 父ID=t.id) 数量
from test t
select 1 as id, 0 as parentid, 1 as ext from dual union all
select 2 as id, 1 as parentid, 2 as ext from dual union all
select 3 as id, 2 as parentid, 3 as ext from dual union all
select 4 as id, 0 as parentid, 4 as ext from dual union all
select 5 as id, 4 as parentid, 5 as ext from dual union all
select 6 as id, 4 as parentid, 6 as ext from dual)select t.id, (select sum(ext) from sd connect by prior id = parentid start with id = t.id) res from sd t;
create table test_table as(
select 1 as id, 0 as parentid, 1 as ext from dual union all
select 2 as id, 1 as parentid, 2 as ext from dual union all
select 3 as id, 2 as parentid, 3 as ext from dual union all
select 4 as id, 0 as parentid, 4 as ext from dual union all
select 5 as id, 4 as parentid, 5 as ext from dual union all
select 6 as id, 4 as parentid, 6 as ext from dual);create or replace view test_view as
select t.id, (select sum(ext) from test_table connect by prior id = parentid start with id = t.id) res from test_table t;
上面的效率都太慢了!