我有桌子如下
帐户
CUSTOMER_ID PAYING_ACCOUNT_ID PARENT_ACCOUNT_ID ACCOUNT_ID COMPANY_ID 24669 24669 24669 24669 0 24671 24671 24669 24671 0 24670 24670 24669 24670 0 3385217 3385217 24670 3385217 0 158 158 158 158 0 159 159 158 159 0 160 160 159 160 0 161 161 160 161 0 162 162 160 162 0 180 180 180 180 0
这是DDL
CREATE TABLE "SYSTEM"."ACCOUNT"
("CUSTOMER_ID" NUMBER(20,0) NOT NULL ENABLE,"PAYING_ACCOUNT_ID" NUMBER(20,0),"PARENT_ACCOUNT_ID" NUMBER(20,"ACCOUNT_ID" NUMBER,"COMPANY_ID" NUMBER)
这是我的查询
select lpad(' ',2*level) || A.ACCOUNT_ID AS LEVEL_LABEL,LEVEL,A.*
from ACCOUNT A
start with PARENT_ACCOUNT_ID IN
(select PARENT_ACCOUNT_ID
from ACCOUNT
where ACCOUNT_ID IN
(select PARENT_ACCOUNT_ID
from ACCOUNT
where parent_account_id != account_id)
and ACCOUNT_ID = PARENT_ACCOUNT_ID)
CONNECT BY NOCYCLE PRIOR A.ACCOUNT_ID = A.PARENT_ACCOUNT_ID;
查询的主要目的是选择具有层次关系的数据,即PARENT_ACCOUNT_ID& ACCOUNT_ID,但是我得到了查询返回的重复数据
任何建议都非常感谢.谢谢
解决方法
为什么不简单：
SELECT level,* FROM accounts
START WITH parent_account_id = account_id
CONNECT BY PRIOR account_id = parent_account_id
AND account_id <> parent_account_id
？
