sql - 仅按最早日期选择唯一 ID

问题描述

我有以下查询，我正在尝试修改以仅获取 1 个PO_ID具有最旧日期的唯一 ( DUE_DT)

SELECT DISTINCT (CONVERT(CHAR(10),B.PO_DT,121)) AS 'ENTERED DATE', B.BUSINESS_UNIT, A.DEPTID, B.PO_ID,
 B.PO_STATUS, B.VENDOR_ID, B.BUYER_ID, 
 D.RECV_REQ, (CONVERT(CHAR(10),E.DUE_DT,121)) AS 'DUE_DT'
FROM ((((PS_PO_HDR B 
LEFT OUTER JOIN  PS_PO_LINE_DISTRIB A ON  B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.PO_ID = A.PO_ID ) 
LEFT OUTER JOIN  PS_VENDOR C ON  C.VENDOR_ID = B.VENDOR_ID ) 
LEFT OUTER JOIN  PS_PO_LINE D ON  
B.BUSINESS_UNIT = D.BUSINESS_UNIT AND B.PO_ID = D.PO_ID ) 
LEFT OUTER JOIN  PS_PO_LINE_SHIP E ON  D.BUSINESS_UNIT = E.BUSINESS_UNIT AND D.PO_ID = E.PO_ID AND D.LINE_NBR = E.LINE_NBR )
 WHERE ( B.PO_STATUS IN ('A','D','O')
   AND D.CANCEL_STATUS IN ('A','X','O','C')
   AND E.DUE_DT BETWEEN dateadd(day, datediff(day, 0, getdate()-11), 0) AND dateadd(day, datediff(day, 0, getdate()), -3)
     AND B.RECV_STATUS NOT IN ('C','M','R')
     AND D.RECV_REQ = 'Y')
  ORDER BY 2, 3, 4

这是我目前得到的样本日期：

ENTERED_DT   BUSINESS_UNIT  DEPTID  PO_ID        PO_STATUS   VENDOR_ID   BUYER_ID   RECV_REQ    DUE_DT
2020-07-13   11000          624     A010035568   D           UNITED      NOTT       Y           2020-07-13
2020-07-08   11000          659     A010035426   D           SCIMED      NOTT       Y           2020-07-15
2020-07-08   11000          659     A010035426   D           SCIMED      NOTT       Y           2020-07-09
2020-07-09   11000          659     A010035468   D           SCIMED      NOTT       Y           2020-07-10

在这种情况下，对于第 3 行和第 4 行（PO_ID重复的位置），我只想返回一行，并且我希望2020-07-09返回具有最早日期 ( ) 的行。

我尝试NOT EXISTS在子句中添加条件，WHERE但现在似乎过滤掉了太多结果：

 ....
    AND NOT EXISTS (SELECT COUNT(*) , BB.DUE_DT
                FROM PS_PO_LINE_SHIP BB
                LEFT OUTER JOIN PS_PO_LINE DD ON DD.BUSINESS_UNIT = BB.BUSINESS_UNIT
                 AND DD.PO_ID = BB.PO_ID AND DD.LINE_NBR = BB.LINE_NBR
                WHERE BB.PO_ID = B.PO_ID
                AND BB.BUSINESS_UNIT = B.BUSINESS_UNIT
                GROUP BY BB.DUE_DT
                HAVING COUNT(*) > 1)

编辑：我更改了查询以获取 MIN DUE_DT （MIN((CONVERT(CHAR(10),E.DUE_DT,121)))但是在尝试计算和之间的天数差异时，PO_DT我DUE_DT收到了 GROUP BY 错误。

我将此列添加到我的 SELECT 语句中： DATEDIFF(DAY, E.DUE_DT, B.PO_DT) AS DateDiff但是它说它从Group By. 如果我将它添加到 Group By 然后我重新引入重复的 PO_ID。有任何想法吗？

标签： sqlsql-server-2014

sql - 仅按最早日期选择唯一 ID

问题描述

解决方案

推荐阅读