mysql - 获取项目的最后状态

Question

在 MySQL 中，我有两个具有 1:n 关系的表。

表项包含产品，其状态保存在另一个表中，如下所示:

项目:

id |ref_num|name    |...
1  |0001   |product1|...
2  |0002   |product2|...

项目状态:

id|product_id|state_id|date
1 |1         |5       |2010-05-05 10:25:20
2 |1         |9       |2010-05-08 12:38:00
3 |1         |6       |2010-05-10 20:45:12
...

states 表不相关，仅将 state_id 与州名称等相关。

如何获取最新状态为我指定的状态(每行一项)的产品？

谢谢

Answer 1

您可能想尝试以下操作:

SELECT i.ref_num, i.name, s.latest_date
FROM   items i
JOIN   (
           SELECT   product_id, MAX(date) as latest_date
           FROM     items_states
           GROUP BY product_id
       ) s ON (s.product_id = i.id);

如果您只想返回一项，只需在查询中添加 WHERE i.id = ? 即可。

测试用例:

CREATE TABLE items (id int, ref_num varchar(10), name varchar(10));
CREATE TABLE items_states (id int, product_id int, state_id int, date datetime);

INSERT INTO items VALUES (1, '0001', 'product1');
INSERT INTO items VALUES (2, '0002', 'product2');

INSERT INTO items_states VALUES (1, 1, 5, '2010-05-05 10:25:20');
INSERT INTO items_states VALUES (2, 1, 9, '2010-05-08 12:38:00');
INSERT INTO items_states VALUES (3, 1, 6, '2010-05-10 20:45:12');

结果:

+---------+----------+---------------------+
| ref_num | name     | latest_date         |
+---------+----------+---------------------+
| 0001    | product1 | 2010-05-10 20:45:12 |
+---------+----------+---------------------+
1 row in set (0.02 sec)