SQL WITH语句

Question

我有下面的问题。为什么invoice_dt设置在1/1/2021 -5/31/2021之间...输出将显示所有日期范围。如何将其设置为在日期范围内基于pay_cte查找dups？

with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as (
select vendor_id,invoice_id, pay_amt,count(*)
from ps_voucher
where invoice_dt between '01-Jan-2021' and '31-May-2021'
group by vendor_id,invoice_id, pay_amt
having count(*)>1)
select t.vendor_id, t.voucher_id,t.INVOICE_ID,t.gross_amt, t.INVOICE_DT
from ps_voucher t
join pay_cte p on t.vendor_id=p.vendor_id and t.invoice_id = p.invoice_id
and t.gross_amt=p.pay_amt

Answer 1

查询返回所有日期的原因是，使用WITH子句的实际select语句没有对日期进行任何过滤。您的ps_voucher表中可能有一些记录的INVOICE_DT值超出了您想知道的日期范围。

有两种方法可以过滤结果；第一种是在(内部) JOIN子句中，第二种是通过在查询中添加WHERE子句。

选项1：

with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as (
    select 
        vendor_id,
        invoice_id, 
        pay_amt,
        count(*)
    from ps_voucher
    where 
        invoice_dt between '01-Jan-2021' and '31-May-2021'
    group by 
        vendor_id,
        invoice_id,
        pay_amt
    having 
        count(*)>1
)
select 
    t.vendor_id, 
    t.voucher_id,
    t.INVOICE_ID,
    t.gross_amt,
    t.INVOICE_DT
from ps_voucher t
join pay_cte p 
    on t.vendor_id = p.vendor_id 
    and t.invoice_id = p.invoice_id
    and t.gross_amt=p.pay_amt
    and t.INVOICE_DT between '01-Jan-2021' and '31-May-2021'

选项2：

with pay_cte( vendor_id,invoice_id, pay_amt,pay_cnt ) as (
    select 
        vendor_id,
        invoice_id, 
        pay_amt,
        count(*)
    from ps_voucher
    where 
        invoice_dt between '01-Jan-2021' and '31-May-2021'
    group by 
        vendor_id,
        invoice_id,
        pay_amt
    having 
        count(*)>1
)
select 
    t.vendor_id, 
    t.voucher_id,
    t.INVOICE_ID,
    t.gross_amt,
    t.INVOICE_DT
from ps_voucher t
join pay_cte p 
    on t.vendor_id = p.vendor_id 
    and t.invoice_id = p.invoice_id
    and t.gross_amt=p.pay_amt
where
    t.INVOICE_DT between '01-Jan-2021' and '31-May-2021'

Answer 2

您必须更改"yyyy-mm-dd“格式的日期