sql - 为每个实体提供最新修订的 mysql 查询

Question

我将数据拆分到两个表中。一个是实体表，它描述了实体的基础知识，另一个是实体数据所在的修订表。如果我知道我可以查询最新版本的实体:

SELECT *
FROM `entities`
  JOIN (SELECT *
        FROM `revisions`
        WHERE `entity_fk` = ?
        ORDER BY `revised` DESC
        LIMIT 0, 1) as `revision`
    ON `entities`.`id` = `revision`.`entity_fk`

但现在我需要列出所有使用其最新修订版数据的实体。

revisions table
+--+---------+---------+--------------------+-------------------+
|id|entity_fk|title    |body                |revised            |
+==+=========+=========+====================+===================+
|01|    01   |Hentity A|This Is tH3 first...|2010-08-30 10:02:45|
|02|    01   |Entity A |This is the first...|2010-08-30 10:16:30|
|03|    02   |Entity B |This is another...  |2010-08-30 10:20:20|

查询应返回的是每个实体的最新修订列表，而不仅仅是指定的实体。

2,1,Entity A,This is the first...,2010-08-30 10:16:30
3,2,Entity B,This is another...,2010-08-30 10:20:20

Answer 1

我建议坚持使用一些简单的东西:

select *
from `entities` e join `revisions` r1
  on e.`id` = r1.`entity_fk`
where r1.`revised` = ( 
   select max(r2.`revised`)
   from `revisions` r2
   where r1.`entity_fk` = r2.`entity_fk`
)

如果 mysql 不支持标量子查询，我建议在带有 group by 和 having 子句的修订版上使用自连接。但是，group by 会很长，因为您必须包括 e 和 r1 中的所有列。

例如

select e.*, r1.*
from `entities` e join `revisions` r1
  on e.`id` = r1.`entity_fk`
join `revisions` r2
  on r1.`entity_fk` = r2.`entity_fk`
group by ...
having r1.`revised` = max(r2.`revised`)