content_id似乎不是主键，它可以保存在两个不同的版本（即3或4）中。您可能希望获取感兴趣的版本，以便使用WHERE version= ?进行筛选，或者如果版本类型无关紧要，则只使用distinct content_id

您需要每个内容ID的最大版本。最好的方法可能是横向连接，从MySQL 8.0.14开始就可以使用。

SELECT 
  cv.contentid, 
  cv.version, 
  cv.contentversionid,
  c.alias,
  c.publishdate AS publish_date, 
  ca.name AS contentattribute_name,
  ca.value AS contentattribute_value
FROM db_name.content c
CROSS JOIN LATERAL
(
  SELECT *
  FROM contentversion
  WHERE contentversion.contentid = c.contentid
  ORDER BY contentversion.version DESC
  LIMIT 1
) cv
INNER JOIN db_name.contentattributes ca ON ca.contentversionid = cv.contentversionid
ORDER BY cv.contentid, ca.name;

最后一个对我有效的查询是（感谢Thorsten Kettner）：

SELECT cv.ContentId, cv.Version, cv.ContentVersionId,
db_name.content.alias AS "alias",
db_name.content.PublishDate AS "publish_date", 
db_name.contentattributes.Name AS "contentattribute_name", 
db_name.contentattributes.Value AS "contentattribute_value"
FROM contentversion cv
INNER JOIN (
    SELECT ContentId, MAX(Version) Version
    FROM contentversion
    GROUP BY ContentId
) b ON cv.ContentId = b.ContentId AND cv.Version = b.Version
JOIN db_name.content
ON db_name.content.ContentId = cv.ContentId
JOIN db_name.contentattributes
ON db_name.contentattributes.ContentVersionId = cv.ContentVersionId

必须从查询中省略version和content_version_id列，因为相同的content_id具有不同的值。然后，使用group by子句删除重复的列，如下所示。

SELECT DISTINCT db_name.contentversion.ContentId as content_id,
 db_name.content.alias as alias,
 db_name.content.PublishDate as publish_date, 
 db_name.contentattributes.Name as contentattribute_name, 
 db_name.contentattributes.Value as contentattribute_value
FROM db_name.contentversion
JOIN db_name.content
ON db_name.content.ContentId = db_name.contentversion.ContentId;