我正在尝试编写一个分层代码,除了所有子/父帐户外,该代码还将显示一个具有帐户的列,该帐户基于表TO_DELETE中的id(子/父)。换句话说:根据表TO_DELETE中的哪个帐户显示父/子帐户?
我的代码看起来:
WITH acc_to_delete( ID ) AS (
select parentaccount from account where accountid in
(select accountid from to_delete )
)
SELECT accountid id_acc
FROM account p
START WITH
EXISTS( SELECT 'X'
FROM to_delete w
WHERE p.accountid = w.accountid
)
CONNECT BY accountid = PRIOR parentaccount
union
SELECT accountid id_acc
FROM account p
START WITH
EXISTS( SELECT 'X'
FROM to_delete w
WHERE p.accountid = w.accountid
)
CONNECT BY prior accountid = parentaccount
它似乎运行良好。结果我得到了一个树帐户。至少我认为:)
CREATE TABLE to_delete
( accountid number);
CREATE TABLE account
( accountid number,
parentaccount number)
所以现在删除包含数据的表时:
3123,
3443,
5646,
7563,
3452,
2346
账户包含:
Accountid parentaccount
3123 5533
3443 3452
5646 4342
7563 1239
3452 5533
2346 1230
5533 6474
6474 1231
1231 1293
1293 null
然后输出应该看起来:
accountid based on
3123 3123
5533 3123
6474 3123
1231 3123
1293 3123
3443 3443
3452 3443
6474 3443
1231 3443
1293 3443
5646 5646
4342 5646
7563 7563
等等。。在SQL中可以这样做吗?还是我需要使用pl-sql?
阅读`CONNECT_BY_ROOT`运算符:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/operators004.htm#i1035022
例如,在此查询中:
SELECT p.accountid, CONNECT_BY_ROOT p.accountid as based_on
FROM account p
START WITH p.accountid IN (select accountid from to_delete)
CONNECT BY accountid = PRIOR parentaccount
对于查询返回的每一行,CCD_ 1将从根行(换句话说,从作为层次结构的起始行的START WITH
条件限定的行)返回值CCD_。