代码之家 › 专栏 › 技术社区 › Savageman

检索每个类别的最后两篇文章

greatest-n-per-group mysql optimization sql

Savageman · 技术社区 · 16 年前

假设我有两个表:博客文章和类别。每个博客文章只属于一个类别,因此这里的两个表之间基本上有一个外键。

GROUP BY会将所有内容分组,并在每个类别中只给我留下一行。但是我想要两个。

我相信执行M个查询(M=我希望从每个类别获得的帖子数量)也会非常容易。第一个查询为每个类别选择第一篇文章(带有GROUPBY)。第二个查询检索每个类别的第二篇文章。等

我只是想知道有没有更好的解决办法。我真的不介意为此进行1+N查询,但出于好奇心和一般SQL知识,我将不胜感激!

提前感谢谁能帮我这个忙。

2 回复 | 直到 16 年前

John Feminella 16 年前

看看这个 MySQL article 关于如何处理任意复杂分组中的前N个事物;这是好东西。您可以尝试以下方法:

SET @counter = 0;
SET @category = '';

SELECT
  *
FROM
(
  SELECT
    @counter := IF(posts.category = @category, @counter + 1, 0) AS counter,
    @category := posts.category,
    posts.*
    FROM
      (
      SELECT
        *
        FROM test
        ORDER BY category, date DESC
      ) posts
) posts
HAVING counter < 2

Quassnoi 16 年前

SELECT  p.*
FROM    (
        SELECT  id,
                COALESCE(
                (
                SELECT  datetime
                FROM    posts pi
                WHERE   pi.category = c.id
                ORDER BY
                        pi.category DESC, pi.datetime DESC, pi.id DESC
                LIMIT 1, 1
                ), '1900-01-01') AS post_datetime,
                COALESCE(
                (
                SELECT  id
                FROM    posts pi
                WHERE   pi.category = c.id
                ORDER BY
                        pi.category DESC, pi.datetime DESC, pi.id DESC
                LIMIT 1, 1
                ), 0) AS post_id
        FROM    category c
        ) q
JOIN    posts p
ON      p.category <= q.id
        AND p.category >= q.id
        AND p.datetime >= q.post_datetime
        AND (p.datetime, p.id) >= (q.post_datetime, q.post_id)

索引 posts (category, datetime, id) 让这一切变得更快。

p.category <= c.id AND p.category >= c.id MySQL 使用 Range checked for each record 哪个索引效率更高。

有关类似问题,请参阅我博客中的这篇文章: