mysql - 查询 : getting the last record for each member trouble cause by another value

Question

例如，我有以下数据

     id      product_id   date  
  ------     ----------   ----
     1           1         1
     2           1         2
     3           1         3
     4           2         1
     5           2         2
     6           2         2
     7           3         1

我想要“每个product_id上最后日期的最后一条记录”的结果数据查询

为了得到结果，我使用如下查询

SELECT a.* FROM test AS a 
  JOIN (SELECT MAX(id) AS id, product_id, MAX(DATE) AS DATE FROM test GROUP BY product_id) AS b 
  ON a.id = b.id AND a.product_id = b.product_id AND a.date = b.date

这次我得到了我想要的结果

    id  product_id    date  
------  ----------  --------
     3           1         3
     6           2         2
     7           3         1

当我添加如下所示的其他数据时出现问题

    id  product_id    date  
------  ----------  --------
     1           1         1
     2           1         2
     3           1         3
     4           2         1
     5           2         2
     6           2         2
     7           3         1
     8           1         3
     9           1         2

并使用相同的查询，结果变成这样

    id  product_id    date  
------  ----------  --------
     6           2         2
     7           3         1

product_id 的值“1”在哪里？

Answer 1

试试这个

SELECT id, product_id, DATE FROM test sitem WHERE product_id IN (1,2,3) AND DATE = (SELECT DATE FROM test WHERE product_id = 
sitem.product_id ORDER BY DATE DESC LIMIT 1) AND id = 
(SELECT id FROM test WHERE product_id = sitem.product_id ORDER BY DATE DESC, 
id DESC LIMIT 1) GROUP BY product_id