mysql - 如何从最后一条记录 = x 的位置进行选择？

Question

我有 2 个不同的表:
文件(id，name，disp)
dossiers_etat (id, id_dossier, open, incharge, date)

dossiers很简单，就是一个名字和id的列表

dossiers_etat 以 1-N 关系链接到文件:1 个文件可以链接到 N 个文件-etat，这要归功于连接:dossiers.id = dossiers_etat.id_dossier

我用dossiers_etat作为时间轴记录我的dossiers的每一次状态变化:1个dossiers今天open="Y"，昨天open="N"
感谢 dossiers_etat.date 或 dossiers_etat.id(最后日期和最后 id 显示最新记录)，我可以找到有关文件的最后一条记录

我如何才能完成此查询以获得 LAST 记录中 open="Y"和 incharge="-"的达析报告列表？

SELECT *
FROM dossiers
JOIN dossiers_etat
ON dossiers.id = dossiers_etat.id_dossier
WHERE dossiers.disp = "Y" AND dossiers_etat.open = "Y" AND dossiers_etat.incharge = "-"
ORDER BY dossiers.id DESC

非常感谢您的帮助!

Answer 1

试试这个:

SELECT *
FROM dossiers B
JOIN dossiers_etat as C
ON B.id = C.id_dossier
WHERE B.disp = "Y" AND C.open = "Y" AND C.incharge = "-"
AND C.date = (SELECT max(date) from dossiers_etat WHERE id_dossier = C.id_dossier)
ORDER BY B.id DESC