我有一个类似的问题在这里解决,但当我尝试解决方案时,我认为它失败了,因为我有不同的设置。
我有一个文档表与…(不幸的是,由于它是旧系统,无法编辑表)
+-------+--------+----------+--------+
| Docid | title | revision | linkid |
+-------+--------+----------+--------+
| 1 | docone | 1 | 1 |
| 2 | doctwo | 1 | 2 |
| 3 | docone | 2 | 1 |
|4 | docone | 3 | 1 |
+-------+--------+----------+--------+
在列出所有文档的页面上,我只想列出每个文档的最新版本。例如,Doc1在修订3上,所以我想要那个,而不是其他2。Doc2只在版本1上,所以请显示那个版本。
基于其他帖子中的问题,我写了我的查询如下......
$query_docs = "
SELECT `document`.*, doctype.*
FROM `document`
INNER JOIN doctype
ON `document`.iddoctypes = doctype.iddoctypes
WHERE `document`.revision = (
SELECT MAX(`document`.revision) AS revision
FROM `document`
)
GROUP BY `document`.linkid
ORDER BY `document`.doccreation DESC";
我不得不链接到另一个表来获取文档类型(只是为了使查询更加困难)。
试试这个,我做了一些小的改变
SELECT document.*, doctype.*
FROM document
INNER JOIN doctype
ON document.iddoctypes = doctype.iddoctypes
WHERE document.revision = (
SELECT MAX(d1.revision)
FROM document d1
WHERE document.linkid = d1.linkid
)
ORDER BY document.doccreation DESC
只是一个大胆的猜测:我认为你必须在(select max(...) ...)
子查询中添加group by title
。
所以完整的语句是这样的:
$query_docs = "
SELECT `document`.*, doctype.*
FROM `document`
INNER JOIN doctype
ON `document`.iddoctypes = doctype.iddoctypes
WHERE `document`.revision = (
SELECT MAX(`document`.revision) AS revision
FROM `document`
--GROUP BY `document`.title
GROUP BY `document`.linkid
)
GROUP BY `document`.linkid
ORDER BY `document`.doccreation DESC";
我错过了什么吗?这看起来很简单…
SELECT x.*
FROM my_table x
JOIN (SELECT title,MAX(revision) max_revision FROM my_table GROUP BY title) y
ON y.title = x.title
AND y.max_revision = x.revision;