mysql - LIMIT 在关节的右侧

Question

我有两个 SQL 表:

collections(id, user_id, name)
files(id, collection_id, name, start_date)

我想通过一个请求检索给定用户的每个集合，每个集合“预览”3 个文件(最多)。

我的第一个(也是唯一的)想法是这样的:

SELECT f.*
    FROM collections c 
    LEFT JOIN files f
    ON f.collection_id = c.id
    WHERE c.user_id = 1 AND f.id IN
        (
            SELECT id
                FROM files
                WHERE collection_id = c.id
                ORDER BY start_date DESC
                LIMIT 3
        )
    ORDER BY c.name, f.start_date DESC

但它不适用于 MySQL，给出:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

你有想法吗？ =/

谢谢。

Answer 1

您可以使用一个利用 GROUP_CONCAT 和 FIND_IN_SET 的小技巧，如下所示:

SELECT
  f.*
FROM
  collections c INNER JOIN files f
  ON c.id = f.collection_id
  INNER JOIN (
    SELECT
      collection_id, GROUP_CONCAT(id ORDER BY start_date DESC) ids
    FROM
      files
    GROUP BY
      collection_id) cf
  ON f.collection_id = cf.collection_id
     AND FIND_IN_SET(f.id, cf.ids)<=3
WHERE c.user_id = 1

请参阅 fiddle here .它不会很快，但是如果您只需要返回几条记录应该没问题。

或者您可以使用此查询:

SELECT
  f.*
FROM
  collections c INNER JOIN files f
  ON c.id = f.collection_id
  INNER JOIN (
    SELECT f.collection_id, f.id
    FROM
      files f LEFT JOIN files f2
      ON f.collection_id = f2.collection_id
         AND (f.start_date < f2.start_date
              OR ((f.start_date = f2.start_date) AND (f.id<f2.id)))
    GROUP BY
      f.id
    HAVING
      COUNT(f2.id)<3
    ) last_three
  ON f.collection_id = last_three.collection_id
     AND f.id = last_three.id