oracle:连接子查询和外部查询

Question

我有两个联合查询，如下所示:

     select   '00/00/0000' as payment_date , h1.customer_no
     from payments h1
     where not exists ( select 1 from payments h2 where h2.customer_no = h1.customer_no and h2.ctype = 'CASH' ) 
     and  h1.customer_no = 400
     group by h1.customer_no

     union

     select to_char(h1.payment_date, 'MM/DD/YYYY') , h1.customer_no
      from payments h1 inner   join ( select customer_no, max(payment_date ) as max_date from payments where ctype = 'CASH'  group by customer_no ) subQ
     on  ( h1.customer_no = subQ.customer_no
           and h1.payment_date = subQ.max_date   ) 
           and  h1.customer_no = 400
     group by h1.payment_date, h1.customer_no 

现在，我想在另一个查询中使用此联合。

  select * from (

    select   '00/00/0000' as payment_date , h1.customer_no
    from payments h1
    where not exists ( select 1 from payments h2 where h2.customer_no = h1.customer_no and h2.ctype = 'CASH' ) 
    and  h1.customer_no = p.customer_no 
    group by h1.customer_no

    union

    select to_char(h1.payment_date, 'MM/DD/YYYY') , h1.customer_no
    from payments h1 inner   join ( select customer_no, max(payment_date ) as max_date from payments where ctype = 'CASH'  group by customer_no ) subQ
    on  ( h1.customer_no = subQ.customer_no
    and h1.payment_date = subQ.max_date   ) 
    and  h1.customer_no = p.customer_no 
    group by h1.payment_date, h1.customer_no ) sq,

  payments p
  where  p.customer_no = 400
  and sq.customer_no = p.customer_no 

当我运行它时，我得到ORA-00904："P"."CUSTOMER_NO"：无效的标识符。我需要将h1.customer_no加入到外部查询customer_no。

我看到过一些带有rank的查询，但我不太明白。如何将内部查询与外部查询连接起来？

提前感谢。

Answer 1

您的付款表是否有customer_no列？这似乎就是你的错误所表明的。

至于如何连接子查询，您可能需要研究分解子查询。您可以这样做：

WITH z AS (
  SELECT ...
  UNION
  SELECT ...
), y AS (
  SELECT ...
)
SELECT ...
FROM y
JOIN z ON y.x = z.x
JOIN some_other_table t ON z.a = t.a