SQL:限制链接到每个联接行的行 [英] SQL: Limit rows linked to each joined row

问题描述

在某些情况下，需要从MySQL查询中获取某些结果集，让我们先来看一下当前查询&然后问我一个问题:

I've certain situation that requires certain result set from MySQL query, let's see the current query first & then ask my question:

SELECT 
    thread.dateline AS tdateline, post.dateline AS pdateline, MIN(post.dateline)
    FROM thread AS thread
        LEFT JOIN post AS post ON(thread.threadid = post.threadid)
        LEFT JOIN forum AS forum ON(thread.forumid = forum.forumid)
    WHERE post.postid != thread.firstpostid
        AND thread.open = 1
        AND thread.visible = 1
        AND thread.replycount >= 1
        AND post.visible = 1
        AND (forum.options & 1)
        AND (forum.options & 2)
        AND (forum.options & 4)
        AND forum.forumid IN(1,2,3)
    GROUP BY post.threadid
    ORDER BY tdateline DESC, pdateline ASC

如您所见，主要是我需要从线程"表中选择线程的日期线，除了每个线程的第二个帖子的日期线，这都是在您在WHERE CLAUSE中看到的条件下进行的.由于每个线程有很多帖子，而且每个线程只需要一个结果，因此我为此使用了GROUP BY CLAUSE.

As you can see, mainly I need to select dateline of threads from 'thread' table, in addition to dateline of the second post of each thread, that's all under the conditions you see in the WHERE CLAUSE. Since each thread has many posts, and I need only one result per thread, I've used GROUP BY CLAUSE for that purpose.

此查询将仅返回一个帖子的日期行及其相关的唯一主题.

This query will return only one post's dateline with it's related unique thread.

我的问题是:

1. 如何限制每个线程的返回线程 每个论坛！假设我只需要5个 线程-最多-是 为每个在中声明的论坛返回 在哪里条款'forum.forumid IN(1,2,3)'，这怎么可能 实现.
2. 有什么建议吗? 优化此查询(当然 在解决了第一点之后?)

1. How to limit returned threads per each forum!? Suppose I need only 5 threads -as a maximum- to be returned for each forum declared in the WHERE CLAUSE 'forum.forumid IN(1,2,3)', how can this be achieved.
2. Is there any recommendations for optimizing this query (of course after solving the first point)?

注释:

• 我不想使用子查询，但是 如果这是唯一可用的解决方案 我会接受的.没有双重查询 推荐的.我确定那里有一个聪明的人 解决这种情况的方法.
• 我正在使用MySQL 4.1+，但如果您知道 另一个引擎的答案，只是 分享.

• I prefer not to use sub-queries, but if it's the only solution available I'll accept it. Double queries not recommended. I'm sure there's a smart solution for this situation.
• I'm using MySQL 4.1+, but if you know the answer for another engine, just share.

预先提供建议:)

推荐答案

也许是这样的问题:，或更确切地说，它是指向

Maybe this SO question: "limit the number of rows to join to in mysql" or more precisely its pointer to How to select the first/least/max row per group in SQL could help.

尽管如此，所有这些都归结为子查询.

It all comes down to subqueries though I think.

这篇关于SQL:限制链接到每个联接行的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！