Oracle SQL 中的 START WITH 和 CONNECT BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9257756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
START WITH and CONNECT BY in Oracle SQL
提问by user871695
I have table as below
我有如下表
ACCOUNT
帐户
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
This is the DDL
这是 DDL
CREATE TABLE "SYSTEM"."ACCOUNT"
("CUSTOMER_ID" NUMBER(20,0) NOT NULL ENABLE,
"PAYING_ACCOUNT_ID" NUMBER(20,0),
"PARENT_ACCOUNT_ID" NUMBER(20,0),
"ACCOUNT_ID" NUMBER,
"COMPANY_ID" NUMBER)
This is my query
这是我的查询
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;
The main objective of the query is to select data that has a hierarchical relationship, which are PARENT_ACCOUNT_ID & ACCOUNT_ID, however i got a duplicate data returned by the query
查询的主要目的是选择具有层次关系的数据，即PARENT_ACCOUNT_ID & ACCOUNT_ID，但是我得到了查询返回的重复数据
Any advice much appreciated. Thanks
任何建议非常感谢。谢谢
回答by Benoit
Why not simply:
为什么不简单：
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
?
?
回答by Jam Eel Ahmed
You can use pl/sql HOST command to call .exe files.
您可以使用 pl/sql HOST 命令来调用 .exe 文件。
