MySql查询: select a list of tag names and for each tag get the most recently tagged albums information

Question

我不太确定如何编写这个查询，它可以在一个查询中完成。案例如下:

我需要选择标签名称列表，并为每个标签获取最近标记的专辑信息。这意味着，如果用户创建名为“Pamela Anderson”的专辑并将该专辑标记为“Blondes”，那么该专辑​​现在就是最近标记为“Blondes”的专辑。可能有 100 个标记为“Blondes”的专辑，但应仅选择最新的数据，具体由 objectTagCreateDate 确定。

预期数据如下所示:

    tagId, tagName,   objectTagCreateDate, albumName,       albumPath
   --------------------------------------------------------------------
    1,     Blondes,   2011-08-23,          Pamela Anderson,   7345
    2,     Celebs,    2011-08-20,          Kate Winslet,    2, 5567
    3,     Musicians, 2011-08-22,          Alicia Keys,       6678

等等

表格如下所示:

Tag
 - tagId
 - tagName

ObjectTag
 - objectType
 - objectId
 - tagId
 - objectTagCreateDate

Album
 - albumId
 - albumName
 - albumPath

加入发生在:

tag.tagId = objectTag.tagId AND objectTag.objectType = 3 and objectTag.objectId = album.albumId

这可以在一个查询中完成吗？如果可以，如何实现？

Answer 1

SELECT t.tagId, t.tagName, o.objectTagCreateDate, a.albumName, a.albumPath
FROM ObjectTag AS o
INNER JOIN (
            SELECT tagId, MAX(objectTagCreateDate) As MaxDate
            FROM ObjectTag
            WHERE ObjectTag.objectType = 3
            GROUP BY tagId
            ) AS t1
ON t1.tagId = o.tagId AND t1.MaxDate = o.objectTagCreateDate
INNER JOIN Tag AS t ON t1.tagId = t.tagId
INNER JOIN Album AS a ON o.objectID = a.albumId

内部选择获取每个tagId的MaxDate，并与ObjectTag连接以获得相应的o.objectID。剩下的只是简单的连接。