MySQL不支持子选择内的limit子句，我该怎么做? [英] MySQL doesn't support the limit clause inside a subselect, how can I do that?

问题描述

我在MySQL 5.1.30上有下表:

I have the following table on a MySQL 5.1.30:

CREATE TABLE  article (
  article_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  category_id int(10) unsigned NOT NULL,
  title varchar(100) NOT NULL,
  PRIMARY KEY (article_id)
);

使用此信息:

1, 1, 'foo'
2, 1, 'bar'
3, 1, 'baz'
4, 1, 'quox'
5, 2, 'quonom'
6, 2, 'qox'

我需要获取所有具有文章的类别的每个类别的前三篇文章.像这样:

I need to obtain the first three articles in each category for all categories that have articles. Something like this:

1, 1, 'foo'
2, 1, 'bar'
3, 1, 'baz'
5, 2, 'quonom'
6, 2, 'qox'

当然工会可以工作:

select * from articles where category_id = 1 limit 3
union
select * from articles where category_id = 2 limit 3

但是数据库中类别的数量未知.另外，顺序应由我在示例中省略的is_sticky和published_date列指定，以简化操作.

But there are an unknown number of categories in the database. Also, the order should specified by an is_sticky and a published_date columns I left out of the examples to simplify.

是否可以构建一个查询来检索此信息?

Is it possible to build a query that retrieves this information?

更新:我尝试了以下似乎可行的方法，除了MySQL不支持子选择内的limit子句之外.您知道模拟极限的方法吗?

UPDATE: I've tried the following which would seemed to work except that MySQL doesn't support the limit clause inside a subselect. Do you know of a way to simulate the limit there?

select *
  from articles a
  where a.article_id in (select f.article_id
                      from articles f
                      where f.category_id = a.category_id
                      order by f.is_sticky, f.published_at
                      limit 3)

谢谢

推荐答案

SELECT ... LIMIT在子查询中不支持LIMIT，现在是时候打破自我结合的魔力了:

SELECT ... LIMIT isn't supported in subqueries, I'm afraid, so it's time to break out the self-join magic:

SELECT article.*
FROM article
JOIN (
    SELECT a0.category_id AS id, MIN(a2.article_id) AS lim
    FROM article AS a0
    LEFT JOIN article AS a1 ON a1.category_id=a0.category_id AND a1.article_id>a0.article_id
    LEFT JOIN article AS a2 ON a2.category_id=a1.category_id AND a2.article_id>a1.article_id
    GROUP BY id
) AS cat ON cat.id=article.category_id
WHERE article.article_id<=cat.lim OR cat.lim IS NULL
ORDER BY article_id;

通过尝试以升序ID顺序连接同一表的三个副本，中间的位正在计算出每个类别的第三低ID文章的ID.如果某个类别的文章少于三篇，则左联接将确保限制为NULL，因此外部WHERE也需要选择这种情况.

The bit in the middle is working out the ID of the third-lowest-ID article for each category by trying to join three copies of the same table in ascending ID order. If there are fewer than three articles for a category, the left joins will ensure the limit is NULL, so the outer WHERE needs to pick up that case as well.

如果您的前3名"要求有时可能会变为前n名"，那么这将变得很笨拙.在这种情况下，您可能需要重新考虑先查询不同类别列表然后合并每个类别查询的想法.

If your "top 3" requirement might change to "top n" at some point, this begins to get unwieldy. In that case you might want to reconsider the idea of querying the list of distinct categories first then unioning the per-category queries.

ETA:在两栏中订购:eek，新要求！ :-)

ETA: Ordering on two columns: eek, new requirements! :-)

这取决于您的意思:如果您只想订购最终结果，则可以毫无疑问地将其敲打.但是，如果您需要使用此顺序来选择要挑选的三篇文章，事情就会变得困难得多.

It depends what you mean: if you're only trying to order the final results you can bang it on the end no problem. But if you need to use this ordering to select which three articles are to be picked things are a lot harder.

我们正在使用带有'<'的自连接来重现'ORDER BY article_id'所具有的效果.不幸的是，虽然您可以执行"OR BY BY a，b"，但您不能执行(a，b)<(c，d)" ...也不能执行"MIN( a，b)'.另外，实际上，您将按issticky，发布的和 article_id的三列进行排序，因为您需要确保每个排序值都是唯一的，以避免返回四行或更多行.

We are using a self-join with ‘<’ to reproduce the effect ‘ORDER BY article_id’ would have. Unfortunately, whilst you can do ‘ORDER BY a, b’, you can't do ‘(a, b)<(c, d)’... neither can you do ‘MIN(a, b)’. Plus, you'd actually be ordering by three columns, issticky, published and article_id, because you need to ensure that each ordering value is unique, to avoid getting four or more rows returned.

虽然您可以通过某些粗略的整数或列的字符串组合来组成自己的可排序值:

Whilst you could make up your own orderable value by some crude integer or string combination of columns:

LEFT JOIN article AS a1
ON a1.category_id=a0.category_id
AND HEX(a1.issticky)+HEX(a1.published_at)+HEX(a1.article_id)>HEX(a0.issticky)+HEX(a0.published_at)+HEX(a0.article_id)

这变得越来越丑陋，并且计算将浪费使用索引来提高查询效率的任何机会.此时，最好只进行单独的每个类别的LIMITED查询.

this is getting unfeasibly ugly, and the calculations will scupper any chance of using the indices to make the query efficient. At which point you are better off simply doing the separate per-category LIMITed queries.

这篇关于MySQL不支持子选择内的limit子句，我该怎么做?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！