如何使用最大日期选择和连接字段？

Question

我有两个关于RECID和AAATRANSPORTORDERRECID的表：

阿特拉斯泰

Pro Number  Bill Date   CREATEDDATETIME         RECID
14521857    2019-04-10  2019-06-04 21:53:09.000 5637146183

阿亚尔昌德

AAAREFNUMVALUE  AAALTLCHANGEVALUE   RECID        CREATEDDATETIME              AAATRANSPORTORDERRECID
14521857        Edit Cycle          5637655326   2020-01-21 14:26:31.000        5637146183
14521857        Ready to Invoice    5637656076   2020-01-21 14:29:24.000        5637146183
14521857        Invoiced            5637656098   2020-01-21 16:04:39.000        5637146183

我需要从AAATRANSPORTTABLE中选择显示的记录，并从AAALTLCHANGEREQUEST加入最近的CREATEDDATETIME的AAALTLCHANGEVALUE值。我的查询如下：

SELECT

    t.[Pro Number],
    t.CREATEDDATETIME,
    t.[Bill Date],
    t.RECID,
    l.AAALTLCHANGEVALUE,
    max(l.CREATEDDATETIME) as Status_Date


FROM [AX2cTestStage].[dbo].[AAATRANSPORTTABLE_V] t
LEFT JOIN AX2cTestAdapter_dbo_AAALTLCHANGEREQUEST_V l 
ON t.RECID = l.AAATRANSPORTORDERRECID

WHERE t.[Pro Number] = '14521857'

GROUP by l.CREATEDDATETIME,t.[Pro Number],t.CREATEDDATETIME,t.[Bill Date],t.RECID,l.AAALTLCHANGEVALUE  

它产生了以下结果：

Pro Number  Bill Date     CREATEDDATETIME              RECID        AAALTLCHANGEVALUE   Status_Date
14521857    2019-04-10    2019-06-04 21:53:09.000     5637146183    Edit Cycle          2020-03-24 11:42:52.000
14521857    2019-04-10    2019-06-04 21:53:09.000     5637146183    Ready to Invoice    2020-03-24 11:51:00.000
14521857    2019-04-10    2019-06-04 21:53:09.000     5637146183    Invoiced            2020-03-24 11:52:08.000

我想要的输出是

Pro Number  Bill Date   CREATEDDATETIME         RECID       AAALTLCHANGEVALUE  Status_Date
14521857    2019-04-10  2019-06-04 21:53:09.000 5637146183  Invoiced           2020-03-24 11:52:08.000

Answer 1

问题是在select查询中选择max(l.CREATEDDATETIME)，但在where子句中没有做任何选择最大日期的操作。您必须放置where子句，以从3行中选择具有最大日期的行。我已经尝试过修改您的查询，您可能需要修改表和列名，但是当您浏览它时，您将得到上下文-

SELECT

    t.[Pro Number],
    t.CREATEDDATETIME,
    t.[Bill Date],
    t.RECID,
    l.AAALTLCHANGEVALUE,
    l.CREATEDDATETIME as Status_Date


FROM [AX2cTestStage].[dbo].[AAATRANSPORTTABLE_V] t
LEFT JOIN AX2cTestAdapter_dbo_AAALTLCHANGEREQUEST_V l 
ON t.RECID = l.AAATRANSPORTORDERRECID

WHERE t.[Pro Number] = '14521857'
AND l.CREATEDDATETIME = (select max(V.CREATEDDATETIME) from AX2cTestAdapter_dbo_AAALTLCHANGEREQUEST_V V where V.AAAREFNUMVALUE = '14521857')

GROUP by l.CREATEDDATETIME,t.[Pro Number],t.CREATEDDATETIME,t.[Bill Date],t.RECID,l.AAALTLCHANGEVALUE

Answer 2

要从您的数据中获得所需的结果是不可能的，因为所需数据中给定的日期甚至在任何表中都不存在。但是，从您的定义来看，这应该是您所追求的：

SELECT

    t.[ProNumber],
    t.CREATEDDATETIME,
    t.[BillDate],
    t.RECID,
    l.AAALTLCHANGEVALUE,
    l.CREATEDDATETIME as Status_Date


FROM [AAATRANSPORTTABLE] t
LEFT JOIN (
    select t1.AAATRANSPORTORDERRECID, t1.AAALTLCHANGEVALUE, t1.CREATEDDATETIME 
    from AAALTLCHANGEREQUEST t1
    inner join (
          select AAATRANSPORTORDERRECID, max(CREATEDDATETIME) as maxDate 
          from AAALTLCHANGEREQUEST
          group by AAATRANSPORTORDERRECID) t2 
          on t1.AAATRANSPORTORDERRECID = t2.AAATRANSPORTORDERRECID 
             and t1.CREATEDDATETIME = t2.MaxDate) l
    ON t.RECID = l.AAATRANSPORTORDERRECID
WHERE t.[ProNumber] = '14521857';

DBFiddle演示

PS:你不需要任何组的主选择，它只需要得到每个组的最大日期。