查找产品的最新发货（sql 子选择？）

Question

我有三个表

shipment (shipment_id, shipping_date)
company_order (company_order_id, shipment_id, company_id)
company_order_item (company_order_item_id, company_order_id, product_id)

几家公司聚集在一起并汇总来自单个制造商的订单。该聚合订单称为“发货”。公司在每次发货中都会订购精选的产品：因此并非所有产品都会出现在任何一次发货中或为任何一家公司提供。

如何编写 SQL 查询来查找每个 Product_id 的最新发货？

我看过 SQL 查询 - 获取最新修订（更简单的情况）。

Answer 1

您需要获取每个产品 ID 的最大发货日期，然后检索发货详细信息，

例如

SELECT  *
FROM    (
            SELECT  coi.product_id,
                    MAX(s.shipping_date) MaxDate
            FROM    company_order_item coi INNER JOIN
                    company_order co ON coi.company_order_id = co.company_order_id INNER JOIN
                    shipment s ON co.shipment_id =s.shipment_id
            GROUP BY coi.product_id
        ) sub INNER JOIN
        company_order_item coi ON sub.product_id = coi.product_id INNER JOIN
        company_order co ON coi.company_order_id = co.company_order_id INNER JOIN
        shipment s ON   co.shipment_id = s.shipment_id
                    AND s.shipping_date = sub.MaxDate

Answer 2

用于说明的 SQL 代码 - （这是 T-SQL，对 SQL Server 友好，但我手边没有任何 mysql。最后一个查询应该进行微小的修改（以适合您的表名称），在 MySQL 中也能很好地工作。

我的逻辑就是找到每个product_id的最新company_order。一旦我有了它，我就可以将company_order_id加入到company_order，并且我有每个product_id的每个最新company_order的shipment_id。

DROP TABLE #shipment
DROP TABLE #company_order
DROP TABLE #company_order_item
CREATE TABLE #shipment
    (
      shipment_id INT ,
      shipping_date INT
    ) ;
CREATE TABLE #company_order
    (
      company_order_id INT ,
      shipment_id INT ,
      company_id INT
    ) ;
CREATE TABLE #company_order_item
    (
      company_order_item_id INT ,
      company_order_id INT ,
      product_id INT
    ) ;

INSERT  INTO #shipment
        ( shipment_id , shipping_date )
VALUES
        ( 1 , 1 ),
        ( 2 , 2 ),
        ( 3 , 3 )

INSERT  INTO #company_order
        ( company_order_id , shipment_id , company_id )
VALUES
        ( 1 , 1 , 1 ),
        ( 2 , 2 , 1 ),
        ( 3 , 3 , 1 )

INSERT  INTO #company_order_item
        ( company_order_item_id , company_order_id , product_id )
VALUES
        ( 1 , 1 , 1 )        ,
        ( 2 , 1 , 2 ),
        ( 2 , 2 , 2 ),
        ( 1 , 1 , 3 ),
        ( 1 , 3 , 4 )

SELECT
    product_id ,
    shipment_id
FROM
    (
      SELECT
        product_id ,
        MAX(company_order_id) AS company_order_id
      FROM
        #company_order_item
      GROUP BY
        product_id
    ) AS MostRecentProductInOrder
INNER JOIN #company_order
ON  MostRecentProductInOrder.company_order_id = #company_order.company_order_id