MySQL Inner Join With LIMIT 到左表

发布于 2024-10-19 11:32:25 字数 303 浏览 3 评论 0原文

我有这个数据库查询

SELECT *
FROM (`metadata` im)
INNER JOIN `content` ic ON `im`.`rev_id`  = `ic`.`rev_id`
WHERE `im`.`id` = '00039'
AND `current_revision` = 1
ORDER BY `timestamp` DESC
LIMIT 5, 5

该查询将结果中的总行数限制为 5。我想将左表元数据限制为 5，而不限制整个结果集。

我应该如何编写查询？

原文

I have this database query

SELECT *
FROM (`metadata` im)
INNER JOIN `content` ic ON `im`.`rev_id`  = `ic`.`rev_id`
WHERE `im`.`id` = '00039'
AND `current_revision` = 1
ORDER BY `timestamp` DESC
LIMIT 5, 5

The query limits the total rows in the result to 5. I want to limit the left table metadata to 5 without limiting the entire result-set.

How should I write the query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

屋顶上的小猫咪 2024-10-26 11:32:25

如果您考虑一下您想要做什么，您实际上并没有针对元数据进行选择。

您需要首先对其进行子查询。

尝试：

SELECT *
FROM ((select * from metadata limit 5) im)
INNER JOIN `content` ic ON `im`.`rev_id`  = `ic`.`rev_id`
WHERE `im`.`id` = '00039'
AND `current_revision` = 1
ORDER BY `timestamp` DESC

If you think about what you are trying to do, you're not really doing a select against metadata.

You need to sub query that first.

Try:

SELECT *
FROM ((select * from metadata limit 5) im)
INNER JOIN `content` ic ON `im`.`rev_id`  = `ic`.`rev_id`
WHERE `im`.`id` = '00039'
AND `current_revision` = 1
ORDER BY `timestamp` DESC

回复收藏 0 原文

情独悲 2024-10-26 11:32:25

这是另一种可能的方法：

SET @serial=0;
SET @thisid=0;

SELECT 
   @serial := IF((@thisid != im.id), @serial + 1, @serial), 
   @thisid := im.id,
   * 
FROM (`metadata` im)
WHERE `im`.`id` = '00039'
AND `current_revision` = 1
AND @serial < 5
ORDER BY `timestamp` DESC

这尚未经过测试。如果您在执行此操作时遇到问题，请告诉我 - 我可以详细说明。

Here is another possible approach:

SET @serial=0;
SET @thisid=0;

SELECT 
   @serial := IF((@thisid != im.id), @serial + 1, @serial), 
   @thisid := im.id,
   * 
FROM (`metadata` im)
WHERE `im`.`id` = '00039'
AND `current_revision` = 1
AND @serial < 5
ORDER BY `timestamp` DESC

This isn't tested. Please let me know if you run into issue implementing this - and I can elaborate.

回复收藏 0 原文

无戏配角 2024-10-26 11:32:25

好吧，我认为你的意思是 LEFT JOIN 尝试使用 LEFT JOIN 而不是 INNER JOIN

    SELECT DISTINCT *
    FROM (`metadata` im)
    INNER JOIN `content` ic ON `im`.`rev_id`  = `ic`.`rev_id`
    WHERE `im`.`id` = '00039'
    AND `current_revision` = 1
    ORDER BY `timestamp` DESC
    LIMIT 5, 5

Well, I think you mean LEFT JOIN try using LEFT JOIN instead of INNER JOIN

    SELECT DISTINCT *
    FROM (`metadata` im)
    INNER JOIN `content` ic ON `im`.`rev_id`  = `ic`.`rev_id`
    WHERE `im`.`id` = '00039'
    AND `current_revision` = 1
    ORDER BY `timestamp` DESC
    LIMIT 5, 5

回复收藏 0 原文

~没有更多了~