mysql - 从联接表中按 MAX(date) 选择

Question

我正在运行这个查询。

SELECT 
  t1.quantity, 
  t2.transaction_date, 
  t1.product_id 
FROM 
  ezsystem_usah.invoice_line AS t1     
  INNER JOIN ezsystem_usah.invoices AS t2 
    ON t1.invoice_id = t2.id_invoices 
WHERE 
  t2.customer_id = '8000004C-1325619329' 
  AND ( product_id = '8000016F-1325198704' 
     OR product_id = '80000027-1324422404' OR ...);

结果表是:

quantity | transaction_date | product_id
7          2012-01-04         8000016F-1325198704
8          2012-03-05         8000016F-1325198704
1          2012-01-05         11111111-1324422404
...        ...                ...

我想根据特定 product_id 的 MAX(transaction_date) 选择行，以便产生如下内容:

quantity | transaction_date | product_id
8          2012-03-05         8000016F-1325198704
1          2012-01-05         11111111-1324422404

换句话说，我想从连接表中选择每个产品 ID 的最新条目。

我试过:

SELECT 
  t1.quantity, 
  MAX(t2.transaction_date), 
  t1.product_id 
FROM 
  ezsystem_usah.invoice_line AS t1 
  INNER JOIN ezsystem_usah.invoices AS t2 ON t1.invoice_id = t2.id_invoices 
WHERE 
  t2.customer_id = '8000004C-1325619329' 
  AND (product_id = '8000016F-1325198704' OR product_id = '80000027-1324422404' OR ...)     
GROUP BY 
 t1.product_id; 

但是数量不正确。

另一种我没有尝试过但我认为可能可行的方法是使用 HAVING MAX(transaction_date) = ([sub query])，但它似乎很昂贵。

最坏的情况是，我可以对每个产品一个一个地运行查询，但我想尽可能避免这种情况。

谢谢，丹麦人

Answer 1

这应该可以满足您的要求并且相当高效。产品在内部循环中使用 IN () 检查，因此如果您在 product_id 和 customer_id 上建立索引，它应该运行得非常快。还建议在 t1.invoice_id 上建立索引。

SELECT 
  t1.quantity, 
  transaction_date, 
  product_id 
FROM 
  ezsystem_usah.invoice_line AS t1     
  JOIN (
    SELECT t3.transaction_date, t3.product_id, t3.id_invoices 
        FROM ezsystem_usah.invoices t3
        JOIN
( SELECT MAX(transaction_date) AS max_transaction_date,
    product_id
    FROM ezsystem_usah.invoices
    WHERE product_id IN (
      '8000016F-1325198704', '80000027-1324422404'
      )
    AND customer_id = '8000004C-1325619329'
    GROUP BY product_id ) AS uniqued
    ON ( t3.transaction_date = uniqued.max_transaction_date
        AND t3.product_id = uniqued.product_id
    )
) AS t2 
ON t1.invoice_id = t2.id_invoices;