在第二个表上加入最近记录的记录

I have 2 tables

我有2张桌子

Delivery
--------
deliveryid int (PK)

description long varchar

DeliveryHistory
---------------
historyid int

delievryid int

statusid int

recordtime timestamp

WHat I am trying to do is a left outer join to bring back all records from table Delivery with only the most recent entry in DeliveryHistory for each delivery. However if there are no entries in the DeliveryHistory for the delivery I would like a null value

我想要做的是左外连接以从表交付中返回所有记录,每个交付只有DeliveryHistory中的最新条目。但是,如果DeliveryHistory中没有用于传递的条目,我想要一个空值

I have done this:

我这样做了:

select d.deliveryid,d.description, h.statusid from delivery d
left outer join  Deliveryhistory h on d.deliveryid = h.deliveryid
where  h.recordtime =
       ( SELECT MAX(recordtime)
           FROM Deliveryhistory
          WHERE deliveryid = d.deliveryid)

But it only returns the rows that have an entry in DeliveryHistory.

但它只返回在DeliveryHistory中有条目的行。

4 个解决方案

#1

Your where clause is resulting in all null values being excluded. Try

您的where子句导致排除所有空值。尝试

where  h.RecordTime is null OR
       h.recordtime =
       ( SELECT MAX(recordtime)
           FROM Deliveryhistory
          WHERE deliveryid = d.deliveryid)

#2

select d.deliveryid,d.description, h.statusid from delivery d
left outer join  Deliveryhistory h on d.deliveryid = h.deliveryid
where  (h.recordtime =
   ( SELECT MAX(recordtime)
       FROM Deliveryhistory
      WHERE deliveryid = d.deliveryid)
  or h.deliveryid = null)

#3

The existing answers are all it takes but if you'd like to do this without using a WHERE clause you can use following construct.

现有的答案都是它需要的但是如果你想在不使用WHERE子句的情况下这样做,你可以使用以下构造。

SELECT  d.deliveryid
        ,d.description
        , dh.statusid
FROM    Delivery d 
        LEFT OUTER JOIN (
          SELECT deliveryid, MAX(recordtime) AS recordtime
          FROM   DeliveryHistory
          GROUP BY
                 deliveryid
        ) dhm ON dhm.deliveryid = d.deliveryid                 
        LEFT OUTER JOIN DeliveryHistory dh ON dh.deliveryid = dhm.deliveryid 
                                              AND dh.recordtime = dhm.recordtime

#4

CTE to yield the maxrow (IFF the implementation supports CTEs ;-) plus simple left join with the CTE.

CTE产生maxrow(IFF实现支持CTE ;-)加上简单的左连接与CTE。

WITH last AS (
        SELECT * FROM Deliveryhistory dh
        WHERE NOT EXISTS (
                SELECT * 
                FROM Deliveryhistory nx
                WHERE nx.deliveryid = dh.deliveryid
                AND nx.recordtime > dh.recordtime -- no one is bigger: dh must be the max
                )
        )
SELECT d.deliveryid, d.description, l.statusid 
FROM delivery d
LEFT JOIN last l ON d.deliveryid = l.deliveryid
        ;

#1