gpt4 book ai didi

mysql - LIMIT 在关节的右侧

转载 作者:行者123 更新时间:2023-11-30 23:13:01 24 4
gpt4 key购买 nike

我有两个 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'

你有想法吗? =/

谢谢。

最佳答案

您可以使用一个利用 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

关于mysql - LIMIT 在关节的右侧,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19126520/

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