在Oracle SQL中开始和连接 [英] START WITH and CONNECT BY in Oracle SQL

问题描述

我的桌子如下

帐户

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,0),
"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，但是查询返回了重复的数据

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

任何建议，不胜感激.谢谢

Any advice much appreciated. Thanks

推荐答案

为什么不简单:

 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

?

这篇关于在Oracle SQL中开始和连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！