我在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'


对于包含文章的所有类别,我需要获取每个类别中的前三篇文章。像这样:

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列指定,以简化。

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

更新:我尝试了以下似乎可行的方法,只是MySQL不支持子选择内的limit子句。您知道模拟极限的方法吗?

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,因此现在该介绍一下自我连接的魔力了:

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也需要处理这种情况。

如果您的“前3名”要求可能在某个时候变为“前n名”,这将变得很笨拙。在这种情况下,您可能想重新考虑先查询不同类别列表,然后再合并每个类别查询的想法。

ETA:在两列上订购:eek,新要求! :-)

这取决于您的意思:如果您只想订购最终结果,则可以毫无疑问地将其付诸实践。但是,如果您需要使用此顺序来选择要挑选的三篇文章,则要困难得多。

我们正在使用带有“
虽然您可以通过一些粗整数或列的字符串组合来构成自己的可排序值:

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查询。

关于sql - MySQL不支持子选择内的limit子句,我该怎么做?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/523015/

10-10 18:25
查看更多