层次结构查询(connect by子句) [英] hierarchy query (connect by clause)

问题描述

我正在尝试使用connect by子句编写查询，但我无法处理.

I'm trying to writte a query using connect by clause but I can't handle with that.

通常我有桌子:

CREATE TABLE "TESTOWA" (
    "ACCOUNT" VARCHAR2(20 BYTE), 
    "PARENT"  VARCHAR2(20 BYTE), 
    "PAYMENT" VARCHAR2(20 BYTE)
);

Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5436','5436','1');
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('4576','3457',null);
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5763','5686','1');
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('5686','5686',null);
Insert into TESTOWA (ACCOUNT,PARENT,PAYMENT) values ('3457','5686',null);  

现在，我要做的是找到一个未填写付款列的帐户(甚至是父帐户) 每个帐户都可以具有指示其他帐户ID的父帐户(父列). 如果我在示例中展示它，可能会更容易:

And now, what I want to do is find an accounts which payment column is not filled (even parent account) Every account can have parent account (parent column) which indicate to other account id. Maybe it will be easier if I present it on example:

 ACCOUNTID | PARENT | PAYMENT
-----------------------------
    5436   |  5436  |    1
    4576   |  3457  |  NULL
    5763   |  5643  |    1
    5686   |  5686  |    1
    3457   |  5686  |  NULL

第一个帐户还可以-付款列已填满. 第二个不好，因为它为null-但是我们可以看到有一个上级帐户，所以现在我们检查(3457 account)，再次付款列为空，但是又有一个上级帐户(5686)，最后有一个付款列填充. 因此，对于上述情况，select应该不显示任何内容 如果Table看起来会怎样:

First account is okay - payment column is filled. Second is not okay because is null - but as we can see there is a parent account so now we check (3457 account) , and again payment column is null but again there is a parent account (5686) and finally there is a payment column filled. So for situatios above select should not present nothing What if Table would looks:

 ACCOUNTID | PARENT | PAYMENT
------------------------------
    5436   |  5436  |    1
    4576   |  3457  |  NULL
    5763   |  5643  |    1
    5686   |  5686  |  NULL
    3457   |  5686  |  NULL

我们可以看到，唯一更改是5686帐户ID旁边的null，因此正确的选择应显示帐户:4576, 3457, 5686

As we can see the only one change is null next to 5686 account id, so correct select should present accounts: 4576, 3457, 5686

推荐答案

SQL提琴

Oracle 11g R2架构设置:

CREATE TABLE "TESTOWA" (   
  ACCOUNT NUMBER(4,0), 
  PARENT  NUMBER(4,0), 
  PAYMENT NUMBER(1,0)
);
Insert into TESTOWA values (5436,5436,1);
Insert into TESTOWA values (5686,5686,null);
Insert into TESTOWA values (5763,5686,1);
Insert into TESTOWA values (3457,5686,1); 
Insert into TESTOWA values (4576,3457,null);

查询1 :

SELECT t.*,
       CONNECT_BY_ROOT( PAYMENT ) AS HAS_PAYED
FROM   TESTOWA t
START WITH
       ACCOUNT = PARENT
OR     PAYMENT = 1
CONNECT BY
       NOCYCLE
       PRIOR ACCOUNT = PARENT
AND    PAYMENT IS NULL

结果 :

Results:

| ACCOUNT | PARENT | PAYMENT | HAS_PAYED |
|---------|--------|---------|-----------|
|    5436 |   5436 |       1 |         1 |
|    3457 |   5686 |       1 |         1 |
|    4576 |   3457 |  (null) |         1 |
|    5686 |   5686 |  (null) |    (null) |
|    5763 |   5686 |       1 |         1 |

这篇关于层次结构查询(connect by子句)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！