如何选择和连接具有最大日期的字段？

wpx232ag 于 2021-07-29 发布在 Java

关注(0)|答案(2)|浏览(302)

我在recid和aaatransportorderrecid上连接了两个表：
aaa传送表

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

aaAltlChange请求

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

我需要从aaatransportable中选择显示的记录，并从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

sql sql-server tsql ssms

来源：https://stackoverflow.com/questions/62305665/how-to-select-and-join-field-with-max-date

2条答案

按热度按时间

nwwlzxa71#

问题是在select查询中选择max（l.createddatetime），但在where子句中没有选择max date。您必须放置where子句，以从3行中选择具有max date的行。我试图修改您的查询，您可能需要修改表和列名，但一旦您通过它，您将获得上下文-

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

赞(0）回复(0）举报 2021-07-29

pzfprimi2#

从数据中获取所需结果是不可能的，因为所需数据中的给定日期甚至不存在于任何表中。然而，从你的定义来看，这应该是你所追求的：

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：您不需要在主选择上有任何分组依据，它只用于获取每个组的最大日期。

赞(0）回复(0）举报 2021-07-29

我来回答

如何选择和连接具有最大日期的字段？

2条答案

相关问题

热门标签

最新问答