在这种情况下列的数量是不定的,没法确定到底需要在select 后面写上多少伪列才符合要求,而且即使实现,前台程序语言取起结果来也会非常难处理。唯一能够实现类似于这种要求的是SYS_CONNECT_BY_PATH
SQL> WITH t as (
2 select 'A' id, 'P' pid from dual union all
3 select 'B' id, 'P' pid from dual union all
4 select 'A1' id, 'A' pid from dual union all
5 select 'A2' id, 'A' pid from dual union all
6 select 'A3' id, 'A' pid from dual union all
7 select 'B1' id, 'B' pid from dual union all
8 select 'B2' id, 'B' pid from dual union all
9 select 'B11' id, 'B1' pid from dual union all
10 select 'B12' id, 'B1' pid from dual union all
11 select 'B111' id, 'B11' pid from dual )
12 select 'P'||SYS_CONNECT_BY_PATH(ID,'/') from t start with pid = 'P' connect by PRIOR id = pid;
'P'||SYS_CONNECT_BY_PATH(ID,'/
--------------------------------------------------------------------------------
P/A
P/A/A1
P/A/A2
P/A/A3
P/B
P/B/B1
P/B/B1/B11
P/B/B1/B11/B111
P/B/B1/B12
P/B/B2
10 rows selected
SQL>
SQL> WITH t as (
2 select 'A' id, 'P' pid from dual union all
3 select 'B' id, 'P' pid from dual union all
4 select 'A1' id, 'A' pid from dual union all
5 select 'A2' id, 'A' pid from dual union all
6 select 'A3' id, 'A' pid from dual union all
7 select 'B1' id, 'B' pid from dual union all
8 select 'B2' id, 'B' pid from dual union all
9 select 'B11' id, 'B1' pid from dual union all
10 select 'B12' id, 'B1' pid from dual union all
11 select 'B111' id, 'B11' pid from dual )
12 select 'P'||SYS_CONNECT_BY_PATH(ID,'/') from t start with pid = 'P' connect by PRIOR id = pid;
'P'||SYS_CONNECT_BY_PATH(ID,'/
--------------------------------------------------------------------------------
P/A
P/A/A1
P/A/A2
P/A/A3
P/B
P/B/B1
P/B/B1/B11
P/B/B1/B11/B111
P/B/B1/B12
P/B/B2
10 rows selected
SQL>
解决方案 »
- 关于oracle登陆权限的
- oracle 无法启动监听
- group by查询问题,要列出不再group by后面的统计字段
- win7在安装oracle 10g和oracle 11g时都会出现如下错误,请教高手们指导
- 关于用户下的表的复制,谁来帮我看看,原题目是英文的,不是很难,大家来帮忙谢谢了
- 一个很头疼的oracle的错误 请高手指点
- 高分求教,数据库中数据为'?'(中文问号),使用JDBC读取后写入文件就变成'?'(ASC问号),怎么办?
- 请教:导出库与导入库的字符集不同,如何能够完成导入?___等待中
- group by 问题
- ORACLE调用存储过程ORA-06550报错
- 有关TCP协议中的端口号和其他端口的区别?解析
- 请问一个截取字符串的问题不是按位数来截取
SYS_CONNECT_BY_PATH +1请问在不知道有多少条记录的情况下如何写这个sql
不知道楼主的“在不知道有多少条记录的情况下如何写这个sql”是何意?
例如:select code,bd.base_dep_code_parent,level
from base_dep bd
where level <3
start with code='24'
connect by prior code = base_dep_code_parent
(Account1, Account2, Account3, Account4,Account5,Account6,Account7,Account8,Account9,Account10,Account11,Account12,AccountName)
select
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 1 then
substr(a, 2, length(a))
else
substr(a,
instr(a, '!', 1, 1)+1,
instr(a, '!', 1, 2) - instr(a, '!', 1, 1)-1)
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 1 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 2
then substr(a, instr(a, '!', 1, 2)+1, length(a))
else
substr(a,
instr(a, '!', 1, 2)+1,
instr(a, '!', 1, 3) - instr(a, '!', 1, 2)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 2 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 3
then substr(a, instr(a, '!', 1, 3)+1, length(a))
else
substr(a,
instr(a, '!', 1, 3)+1,
instr(a, '!', 1, 4) - instr(a, '!', 1, 3)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 3 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 4
then substr(a, instr(a, '!', 1, 4)+1, length(a))
else
substr(a,
instr(a, '!', 1, 4)+1,
instr(a, '!', 1, 5) - instr(a, '!', 1, 4)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 4 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 5
then substr(a, instr(a, '!', 1, 5)+1, length(a))
else
substr(a,
instr(a, '!', 1, 5)+1,
instr(a, '!', 1, 6) - instr(a, '!', 1, 5)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 5 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 6
then substr(a, instr(a, '!', 1, 6)+1, length(a))
else
substr(a,
instr(a, '!', 1, 6)+1,
instr(a, '!', 1, 7) - instr(a, '!', 1, 6)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 6 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 7
then substr(a, instr(a, '!', 1, 7)+1, length(a))
else
substr(a,
instr(a, '!', 1, 7)+1,
instr(a, '!', 1, 8) - instr(a, '!', 1, 7)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 7 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 8
then substr(a, instr(a, '!', 1, 8)+1, length(a))
else
substr(a,
instr(a, '!', 1, 8)+1,
instr(a, '!', 1, 9) - instr(a, '!', 1, 8)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 8 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 9
then substr(a, instr(a, '!', 1, 9)+1, length(a))
else
substr(a,
instr(a, '!', 1, 9)+1,
instr(a, '!', 1, 10) - instr(a, '!', 1, 9)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 9 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 10
then substr(a, instr(a, '!', 1, 10)+1, length(a))
else
substr(a,
instr(a, '!', 1, 10)+1,
instr(a, '!', 1, 11) - instr(a, '!', 1, 10)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 10 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 11
then substr(a, instr(a, '!', 1, 11)+1, length(a))
else
substr(a,
instr(a, '!', 1, 11)+1,
instr(a, '!', 1, 12) - instr(a, '!', 1, 11)-1)
end
end,
case
when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) > 11 then
case when LENGTH(REGEXP_REPLACE(REPLACE(a, '!', '@'), '[^@]+', '')) = 12
then substr(a, instr(a, '!', 1, 12)+1, length(a)-instr(a, '!', 1, 12))
else
substr(a,
instr(a, '!', 1, 12)+1,
instr(a, '!', 1, 13) - instr(a, '!', 1, 12)-1)
end
end,
name
from (select sys_connect_by_path(id, '!') a, name
from parent
start with pid = 'Account'
connect by PRIOR id = pid
) t;
谢谢楼上的朋友们,问题已经解决,sql可以实现,发上来留个档