有两个表的Sql层次结构查询

问题描述：

我有两个单独的oracle表，每个都有一个层次结构。它们由ACCOUNT_TYPE键相关。表1定义如此。有两个表的Sql层次结构查询

CREATE TABLE ACCOUNTS(
    ACCOUNT_CODE VARCHAR2(6), 
    ACCOUNT_PRED VARCHAR2(6), 
    ACCOUNT_TYPE VARCHAR2(6), 
    ACCOUNT_TITLE VARCHAR2(100) 
    );

表2定义如这样

CREATE TABLE ACCOUNT_TYPES(
    ACCOUNT_TYPE VARCHAR2(6), 
    ACCOUNT_NUMBER_PRED VARCHAR2(6), 
    ACCOUNT_TITLE VARCHAR(100) 
    );

表1包含以下数据

Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0001',null,'11','xxxx'); 
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0042','0070','13','xxxx'); 
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0054','0110','13','xxxx'); 
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0056','0070','13','xxxx'); 
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0070',null,'13','xxxx'); 
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0110',null,'13','xxxx'); 
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0172','0171','13','xxxx'); 
Insert into HR.ACCOUNTS (ACCOUNT_CODE,ACCOUNT_PRED,ACCOUNT_TYPE,ACCOUNT_TITLE) values ('0060','0001','11','XXXX');

表中的两个包含以下数据

REM INSERTING into ACCOUNT_TYPES 
SET DEFINE OFF; 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('10',null,'xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('11','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('12','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('13','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('14','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('15','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('16','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('17','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('18','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('19','10','xxxx'); 
Insert into ACCOUNT_TYPES (ACCOUNT_TYPE,ACCOUNT_NUMBER_PRED,ACCOUNT_TITLE) values ('1A','10','xxxx');

我可以运行层级查询就像这样

SELECT lpad(' ', (level -1) * 3) || ACCOUNT_CODE AS ACCOUNT_CODE, 
      ACCOUNT_TITLE TITLE, 
      ACCOUNT_PRED PRED, 
      ACCOUNT_TYPE ATYPE 
    FROM ACCOUNTS 
    CONNECT BY PRIOR ACCOUNT_CODE = ACCOUNT_PRED 
    START WITH  ACCOUNT_PRED IS NULL

，另一个像这样

SELECT lpad(' ', (level -1) * 3) ||ACCOUNT_TYPE , 
      ACCOUNT_TITLE, 
      ACCOUNT_NUMBER_PRED 
    FROM ACCOUNT_TYPES 
    CONNECT BY PRIOR ACCOUNT_TYPE = ACCOUNT_NUMBER_PRED 
    START WITH ACCOUNT_NUMBER_PRED IS NULL;

查询一个将基本上返回这些值

我想先拿到帐户类型层次结构中的所以不是我试图产生这种结果。

谁能帮我制作一个查询，将基本上包括账户类型作为第一个层次，然后在它们下面一组，将这些账户在报告帐户。

任何帮助将不胜感激。

删除MySQL的标签。 –

答

如果帐户和帐户类型的密钥是不连贯的，则此查询会根据层次结构以顶级帐户类型级别丰富您的数据集。（如果帐户a类型的密钥可以相同 - 添加一些不同的前缀）。

select ACCOUNT_CODE, nvl(ACCOUNT_PRED,ACCOUNT_TYPE) ACCOUNT_PRED, ACCOUNT_TITLE from ACCOUNTS 
union all 
select ACCOUNT_TYPE ACCOUNT_CODE, null ACCOUNT_PRED, ACCOUNT_TITLE from ACCOUNT_TYPES where ACCOUNT_TYPE in (
    select ACCOUNT_TYPE from ACCOUNTS where ACCOUNT_PRED is NULL)

注意，使用NVL从顶层（空）到相应的帐户类型的上部的简单开关。第二部分添加缺少的帐户类型级别。

使用此作为来源只需应用您的分层查询。

with acc as (  
select ACCOUNT_CODE, nvl(ACCOUNT_PRED,ACCOUNT_TYPE) ACCOUNT_PRED, ACCOUNT_TITLE from ACCOUNTS 
union all 
select ACCOUNT_TYPE ACCOUNT_CODE, null ACCOUNT_PRED, ACCOUNT_TITLE from ACCOUNT_TYPES where ACCOUNT_TYPE in (
    select ACCOUNT_TYPE from ACCOUNTS where ACCOUNT_PRED is NULL) 
) 
SELECT lpad(' ', (level -1) * 3) || ACCOUNT_CODE AS ACCOUNT_CODE, 
      ACCOUNT_TITLE TITLE, 
      ACCOUNT_PRED PRED 
    FROM ACC 
    CONNECT BY PRIOR ACCOUNT_CODE = ACCOUNT_PRED 
    START WITH  ACCOUNT_PRED IS NULL;

其产生的结果预期：由于您使用的是`CONNECT WITH`甲骨文支持

ACCOUNT_CODE TITLE PRED 
--------------- ------ ------ 
11    xxxx   
    0001   xxxx 11  
     0060  XXXX 0001 
13    xxxx   
    0070   xxxx 13  
     0042  xxxx 0070 
     0056  xxxx 0070 
    0110   xxxx 13  
     0054  xxxx 0110

谢谢Marmite，绝对是太棒了！我非常感谢你的回应。 – Miguel

答

进行查询，将preduce你的层次所需要的数据：在您的预解码可为acount型

select ACCOUNT_CODE, nvl(ac.ACCOUNT_PRED, ac.ACCOUNT_TYPE) 
from ACCOUNT_TYPES at 
join ACCOUNTS ac on (at.ACCOUNT_TYPE = ac.ACCOUNT_TYPE)

这会给你一个acount“表”（集）。现在你可以有两个表，并在结果上做一个层次之间的联盟：

select * 
from 
(SELECT ACCOUNT,PRED 
FROM ACCOUNT_TYPES 

union all 

select ACCOUNT, nvl(ac.ACCOUNT_PRED, ac.ACCOUNT_TYPE) as PRED 
from ACCOUNT_TYPES at 
join ACCOUNTS ac on (at.ACCOUNT_TYPE = ac.ACCOUNT_TYPE)) 
CONNECT BY PRIOR ACCOUNT = PRED 
START WITH PRED IS NULL

说实话我还没有运行它，所以我不知道，如果它的工作原理，但这个想法应该是好的。希望能帮助到你。

有两个表的Sql层次结构查询

相关推荐