gpt4 book ai didi

mysql - 从先前查询的结果中提取最新版本的文章

转载 作者:行者123 更新时间:2023-11-29 06:41:43 26 4
gpt4 key购买 nike

我有以下查询:

SELECT e_c.*, c.name, j.status, j.version, j.articleId, j.title FROM assetcategory AS c
INNER JOIN assetentries_assetcategories AS e_c
ON c.categoryId = e_c.categoryId AND c.name = 'news'
INNER JOIN assetentry AS e
ON e.entryId = e_c.entryId
INNER JOIN journalarticle AS j
ON j.resourcePrimKey = e.classPK
AND e.classNameId = (SELECT classNameId FROM classname_ WHERE value = 'com.liferay.portlet.journal.model.JournalArticle')
AND j.companyId= e.companyId
WHERE j.status = 0

它返回journalarticles中的所有类别news。我需要从结果中为每个 articleId 选择最新版本。例如,假设有一篇文章有​​ 4 个版本,即使标题不同,它也是同一篇文章,因为它具有相同的 articleId。因此,对于每个唯一的articleId,我需要最新的版本。我该怎么做?

最佳答案

向子查询添加一个联接,以查找每篇文章的最新版本:

SELECT e_c.*, c.name, j1.status, j1.version, j1.articleId, j1.title
FROM assetcategory AS c
INNER JOIN assetentries_assetcategories AS e_c
ON c.categoryId = e_c.categoryId AND c.name = 'news'
INNER JOIN assetentry AS e
ON e.entryId = e_c.entryId
INNER JOIN journalarticle AS j1
ON j1.resourcePrimKey = e.classPK AND
e.classNameId = (SELECT classNameId FROM classname_
WHERE value = 'com.liferay.portlet.journal.model.JournalArticle') AND
j.companyId = e.companyId
INNER JOIN
(
SELECT articleId, MAX(version) AS max_version
FROM journalarticle
WHERE status = 0
GROUP BY articleId
) j2
ON j1.articleId = j2.articleId AND j1.version = j2.max_version;

连接到上面别名为 j2 的子查询背后的基本思想是,它将结果集限制为仅每篇文章的最新版本。我们不一定要更改查询的其余部分。

关于mysql - 从先前查询的结果中提取最新版本的文章,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51328142/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com