预言：与外部查询

• Z时代
• 2024-01-10
• 分类：问答

加入子查询我有一个联合两个查询如下：预言：与外部查询

 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。

我已经看到一些排名查询，但我无法弄清楚。如何使用外部查询加入内部查询？

在此先感谢。

回答：

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 

以上是 预言：与外部查询 的全部内容， 来源链接： utcz.com/qa/266676.html