如何在子查询聚合中的最近时间记录的另一个表中查找记录

debugcn 发表于 Dev

维农

select sl.*,

       (select pnd_invoiceno 
        from   PINVDET 
        where  PND_INVNO = sl.invno and

               abs(DATEDIFF(ss, pnd_date, sl.adjustedon)) = 
               (select min(abs(DATEDIFF(ss, pnd_date, sl.adjustedon)))
                from   PINVDET
                where  pnd_invno = sl.invno))

from   vwstocklog sl where sl.invno in (select invno from vwStockDiff) 
order  by sl.invno, sl.adjustedon

当我运行上面的查询时，我得到了错误：

在包含外部引用的聚合表达式中指定了多个列。如果要聚合的表达式包含外部引用，则该外部引用必须是表达式中唯一引用的列。

我理解它是说，表达min(abs(DATEDIFF(ss, pnd_date, sl.adjustedon)))的问题，因为它引用sl.adjusted上的min()合计，而且不能这样做，除非它在聚集表达式中引用的唯一列。我不确定该如何解决它。

我在这里尝试做的是pnd_invoiceno在记录中找到pinvdet与同一项目pnd_date最接近的值sl.adjustedon（并且我认识到这有可能链接到多个记录）。

关于如何调整此查询以实现该目标的任何想法？

劳伦斯

第二次尝试（先过滤）：

With x as (
    select
        sl.invno,
        sl.adjustedon,
        p.pnd_invoiceno,
        rank() over (
            partition by sl.invno 
            order by abs(datediff(ss, p.pnd_date, sl.adjustedon))
       ) rk
    from
        vwstocklog sl 
            inner join
        pinvdet p
            on p.pnd_invno = sl.invno
    Where 
        Exists (
            Select
                'x'
            From
                vwStockDiff sd
            Where
                sl.invno = sd.invno
        )
)
Select
    x.invno,
    x.adjustedon,
    x.pnd_invoiceno
From
    x
Where
    x.rk = 1
order by 
    x.invno, 
    x.adjustedon

第一次尝试：

With x as (
    select
        sl.invno,
        sl.adjustedon,
        p.pnd_invoiceno,
        rank() over (
            partition by sl.invno 
            order by abs(datediff(ss, p.pnd_date, sl.adjustedon))
       ) rk
    from
        vwStockDiff sd
            inner join
        vwstocklog sl 
            on sl.invno = sd.invno
            inner join
        pinvdet p
            on p.pnd_invno = sl.invno
    )
Select
    x.invno,
    x.adjustedon,
    x.pnd_invoiceno
From
    x
Where
    x.rk = 1
order by 
    x.invno, 
    x.adjustedon

如果您碰巧有两次相等的距离，这将为两者返回一行。如果只喜欢1.，请替换rank()为row_number()。

SQLFiddle目前似乎无法正常工作，因此我无法对其进行测试。可能存在语法错误。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。