mysql实现分组取每组中前N条_mysql 分组查询统计数量取每组的前n条-优快云博客

本文介绍如何使用MySQL实现分组并选取每组中的前N条记录。通过设置排名来筛选所需数据，并展示了实际操作步骤及结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql实现分组取每组中前N条

准备工作

创建表:

CREATE TABLE `t_post` (
  `id` int(11) DEFAULT NULL,
  `tid` int(11) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message` varchar(255) DEFAULT NULL,
  `dateline` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
)

初始化数据:

INSERT
  INTO `ssm`.`t_post` (`id`, `tid`, `subject`, `message`, `dateline`)
VALUES ('1', '1', 'how are you', 'good', '2018-02-11 15:14:43'),
('2', '1', 'how are you', 'fine, thanks', '2018-02-06 18:15:28'),
('3', '1', 'how are you', 'just so so', '2018-02-06 15:15:16'),
('4', '2', '最近怎么样？', '挺好的', '2018-02-11 15:15:58'),
('5', '2', '最近怎么样？', '一般啦！！', '2018-02-11 15:16:11'),
('6', '2', '最近怎么样？', '非常好，谢谢', '2018-02-11 15:16:29'),
('7', '2', '最近怎么样？', '不好，我头痛', '2018-02-02 15:09:58'),
('8', '1', 'how are you', 'pretty good, man', '2018-02-11 15:15:39'),
('9', '1', 'how are you', 'so bad, I got a headache!', '2018-02-07 11:08:43');

查询数据:

mysql> select * from t_post;
+----+-----+--------------+---------------------------+---------------------+
| id | tid | subject      | message                   | dateline            |
+----+-----+--------------+---------------------------+---------------------+
|  1 |   1 | how are you  | good                      | 2018-02-11 15:14:43 |
|  2 |   1 | how are you  | fine, thanks              | 2018-02-06 18:15:28 |
|  3 |   1 | how are you  | just so so                | 2018-02-06 15:15:16 |
|  4 |   2 | 最近怎么样？ | 挺好的                    | 2018-02-11 15:15:58 |
|  5 |   2 | 最近怎么样？ | 一般啦！！                | 2018-02-11 15:16:11 |
|  6 |   2 | 最近怎么样？ | 非常好，谢谢              | 2018-02-11 15:16:29 |
|  7 |   2 | 最近怎么样？ | 不好，我头痛              | 2018-02-02 15:09:58 |
|  8 |   1 | how are you  | pretty good, man          | 2018-02-11 15:15:39 |
|  9 |   1 | how are you  | so bad, I got a headache! | 2018-02-07 11:08:43 |
+----+-----+--------------+---------------------------+---------------------+
9 rows in set

实践

分组后取每组中最新的一条

SELECT
    tp.id,
    tp.tid,
    tp.`subject`,
    tp.message,
    tp.dateline
FROM
    (
        SELECT
            tp.id,
            tp.tid,
            tp.`subject`,
            tp.message,
            tp.dateline
        FROM
            t_post tp
        ORDER BY
            dateline DESC
    ) tp
GROUP BY
    tp.tid

结果:

+----+-----+--------------+------------------+---------------------+
| id | tid | subject      | message          | dateline            |
+----+-----+--------------+------------------+---------------------+
|  8 |   1 | how are you  | pretty good, man | 2018-02-11 15:15:39 |
|  6 |   2 | 最近怎么样？ | 非常好，谢谢     | 2018-02-11 15:16:29 |
+----+-----+--------------+------------------+---------------------+
2 rows in set

分组后取每组中前N条[排序字段]

主要思想:为每组设立一个排名,最后根据排名取结果

SELECT
    ranked.id,
    ranked.tid,
    ranked.`subject`,
    ranked.message,
    ranked.dateline,
    ranked.rank
FROM
    (
        SELECT
            tp.id,
            tp.tid,
            tp.`subject`,
            tp.message,
            tp.dateline,
            @rank := if(@curr_cur=tp.tid, @rank+1,1) as rank,
            @curr_cur := tp.tid as dummy
        FROM
            t_post tp,
            (SELECT @rank:=0, @curr_cur:=0) temp
        ORDER BY
            tp.tid,dateline DESC //一定要给分组字段排序，不然是错误的!!!这里是tid
    ) as ranked
WHERE
ranked.rank <= 3

结果:

+----+-----+--------------+---------------------------+---------------------+------+
| id | tid | subject      | message                   | dateline            | rank |
+----+-----+--------------+---------------------------+---------------------+------+
|  8 |   1 | how are you  | pretty good, man          | 2018-02-11 15:15:39 |    1 |
|  1 |   1 | how are you  | good                      | 2018-02-11 15:14:43 |    2 |
|  9 |   1 | how are you  | so bad, I got a headache! | 2018-02-07 11:08:43 |    3 |
|  6 |   2 | 最近怎么样？ | 非常好，谢谢              | 2018-02-11 15:16:29 |    1 |
|  5 |   2 | 最近怎么样？ | 一般啦！！                | 2018-02-11 15:16:11 |    2 |
|  4 |   2 | 最近怎么样？ | 挺好的                    | 2018-02-11 15:15:58 |    3 |
+----+-----+--------------+---------------------------+---------------------+------+
6 rows in set