Question

我与甲骨文数据库合作,

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)
)

现在,让我们说,顾客“菠菜帝国”是连锁“菠菜经销商公司”的成员。后者反过来是大连锁“绿宝石R US ” 的一部分,反过来又是“绿宝石R US ” 。当一场针对“绿宝石R US ” 的运动展开时,Popeye有资格参加这场运动。我的询问将只返回“波叶”被专门添加的竞选活动,或者针对“斯皮纳赫经销商公司”的竞选活动。

我怎样才能修改我的查询,把客户间接参与的运动包括在内?

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
        )

友情链接