mysql - MySQL 中使用 LIMIT 左连接子选择

Question

我有 3 张 table :

actor

|    FIELD |             TYPE | NULL | KEY | DEFAULT |          EXTRA |
|----------|------------------|------|-----|---------|----------------|
| actor_id | int(10) unsigned |   NO | PRI |  (null) | auto_increment |
| username |      varchar(30) |   NO |     |  (null) |                |


tag
|  FIELD |             TYPE | NULL | KEY | DEFAULT |          EXTRA |
|--------|------------------|------|-----|---------|----------------|
| tag_id | int(10) unsigned |   NO | PRI |  (null) | auto_increment |
|  title |      varchar(40) |   NO |     |  (null) |                |

actor_tag_count
|            FIELD |             TYPE | NULL | KEY |           DEFAULT |                       EXTRA |
|------------------|------------------|------|-----|-------------------|-----------------------------|
|         actor_id | int(10) unsigned |   NO | PRI |            (null) |                             |
|           tag_id | int(10) unsigned |   NO | PRI |            (null) |                             |
|       clip_count | int(10) unsigned |   NO |     |            (null) |                             |
| update_timestamp |        timestamp |   NO |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

SQLFiddle

我想获取每个 Actor 的 5 个最频繁(最高 clip_count)和最近更新(最新 update_timestamp)标签。

我尝试的查询是:

SELECT
    `a`.`actor_id`,
    `a`.`username`,
    GROUP_CONCAT(atc.clip_count) AS `tag_clip_counts`,
    GROUP_CONCAT(t.tag_id) AS `tag_ids`,
    GROUP_CONCAT(t.title) AS `tag_titles`
FROM
    `actor` AS `a`
LEFT JOIN (
    SELECT
        `atc`.`actor_id`,
        `atc`.`tag_id`,
        `atc`.`clip_count`
    FROM
        `actor_tag_count` AS `atc`
    INNER JOIN `actor` AS `a` USING (actor_id)
    ORDER BY
        atc.clip_count DESC,
        atc.update_timestamp DESC
    LIMIT 5
) AS `atc` USING (actor_id)
LEFT JOIN `tag` AS `t` ON atc.tag_id = t.tag_id
GROUP BY
    `a`.`actor_id`

问题是左连接子选择仅计算一次，并且集合中每个结果的标签仅从 5 个标签的池中获取。

基努·里维斯的预期 GROUP_CONCAT 标签标题结果:

喜剧、科幻、 Action 、悬疑、西部(西部片和纪录片的 clip_count 均为 2，但 西部片 应该排在第一位，因为它的 update_timestamp 较晚)

我不确定这是否有任何相关性，但我正在 actor 表上执行其他连接，但已针对此问题删除了它们。最好只进行 1 个查询，但即使有 2 个查询，我也很难知道如何做到这一点。 1 或 2 查询解决方案受到赞赏。

Answer 1

SQLFiddle ，在非常好的帮助下answer关于使用 GROUP_CONCAT 限制解决方法:

SELECT
    `a`.`actor_id`,
    `a`.`username`,
    SUBSTRING_INDEX(GROUP_CONCAT(atc.clip_count ORDER BY atc.clip_count DESC, atc.update_timestamp DESC), ',', 5) AS `tag_clip_counts`,
    SUBSTRING_INDEX(GROUP_CONCAT(t.tag_id ORDER BY atc.clip_count DESC, atc.update_timestamp DESC), ',', 5) AS `tag_ids`,
    SUBSTRING_INDEX(GROUP_CONCAT(t.title ORDER BY atc.clip_count DESC, atc.update_timestamp DESC), ',', 5) AS `tag_titles`
FROM
    `actor` AS `a`
LEFT JOIN actor_tag_count AS `atc` USING (actor_id)
LEFT JOIN `tag` AS `t` ON atc.tag_id = t.tag_id
GROUP BY
    `a`.`actor_id`