如何在Oracle中从层次结构查询

Question

我使用的是一个包含顾客、连锁店和营销活动表格的Oracle数据库，如下所示（简化版）：

TABLE CUSTOMER 
  ACCOUNTNUMBER   VARCHAR2
  CHAIN           VARCHAR2
  CATEGORY        VARCHAR2

TABLE CHAIN
  CODE            VARCHAR2
  PARENTCHAIN     VARCHAR2

TABLE CAMPAIGN
  ID              NUMBER
  DISCOUNT        NUMBER

TABLE CAMPAIGN_ELIGIBILITY
  CAMPAIGNID      NUMBER   --ID of the campaign
  ACCOUNTNUMBER   VARCHAR2 --Customer included in the campaign
  CHAINCODE       VARCHAR2 --Chain included in the campaign
  CUSTCATCODE     VARCHAR2 --Customercategory included in the campaign

一位顾客可以通过其账户号码、所属的客户组或者是某个连锁店会员身份来符合特定活动的资格。

我使用这个查询来查找所有适用于给定顾客的活动：

select * from campaign where id in
(
  select unique campaignid from campaign_eligibility where 
    accountnumber=:accountnumber
  union
  select unique campaignid from campaign_eligibility where 
    chaincode = 
      (select chain from debtable where accountnumber=:accountnumber)
  union
  select unique campaignid from campaign_eligibility where 
    custcatcode =
      (select category from customer where accountnumber=:accountnumber)
) 

现在，假设客户“Popeye Spinach Empire”是连锁店“Spinach Dealers Inc.”的成员，而后者又是更大的连锁店“GreenFud R US”的一部分，而后者又是“FoAC”连锁店的一部分。当有一个针对“GreenFud R US”的活动时，Popeye就有资格参加该活动。我的查询仅返回Popeye已被特别添加或以“Spinach Dealers Inc.”为目标的活动。

如何修改我的查询以包括客户间接参与的活动？

- Svein Bringsli

Answer 1

WITH    chains AS
        (
        SELECT  code
        FROM    chain c
        START WITH
                c.code IN
                (
                SELECT  chain
                FROM    campaign_eligibility
                WHERE   accountnumber = :acc
                UNION ALL
                SELECT  chain
                FROM    customer c
                JOIN    campaign_eligibility ce
                ON      ce.custcatcode = c.category
                WHERE   accountnumber = :acc
                UNION ALL
                SELECT  chain
                FROM    customer
                WHERE   accountnumber = :acc
                )
        CONNECT BY
                c.code = PRIOR c.parentchain
        ) ch
SELECT  *
FROM    campaign
WHERE   c.id IN
        (
        SELECT  campaignid
        FROM    chains 
        JOIN    campaign_eligibility ce
        ON      ce.chaincode = c.code
        )

- Quassnoi