按组排序，然后按组排序 - order by group then group by

order by group then group by

本文关键字：排序然后 | 更新日期: 2023-09-27

我有一个phpbb-mysql表，它创建了一行，其中包含post_id topic_id和forum_id列。

对于每个帖子，一行的数据是：

post_id topic_id forum_id
2       1        12
1       1        12
3       2        12
4       2        12
6       3        12
5       3        12
2       1        13
1       1        13
3       2        13
4       2        13
6       3        13
5       3        13

我只想得到每个topic_id的min-post_id的行，以获得如下内容：

post_id topic_id forum_id
1       1        12
3       2        12
5       3        12
1       1        13
3       2        13
5       3        13

有人能帮我吗？（我考虑使用order by then group by…，但没有像我想象的那样工作…

试试这样的。。。

select post_id, topic_id, forum_id 
from thetable t
where 
    (post_id,topic_id) = (select min(t1.post_id), t1.topic_id
               from thetable t1 
               where t1.topicid = t.topicid group by t1.topic_id)

这意味着从subselect模式中post_id和topic_id所在的表中获取所有记录。

SQL Fiddle

MySQL 5.6架构设置:

CREATE TABLE topic
    (`post_id` int, `topic_id` int, `forum_id` int)
;
INSERT INTO topic
    (`post_id`, `topic_id`, `forum_id`)
VALUES
    (2, 1, 12),
    (1, 1, 12),
    (3, 2, 12),
    (4, 2, 12),
    (6, 3, 12),
    (5, 3, 12),
    (2, 1, 13),
    (1, 1, 13),
    (3, 2, 13),
    (4, 2, 13),
    (6, 3, 13),
    (5, 3, 13)
;

查询1：

select forum_id, topic_id, min(post_id) as min_post_id
from topic
group by forum_id, topic_id

结果：

| forum_id | topic_id | min_post_id |
|----------|----------|-------------|
|       12 |        1 |           1 |
|       12 |        2 |           3 |
|       12 |        3 |           5 |
|       13 |        1 |           1 |
|       13 |        2 |           3 |
|       13 |        3 |           5 |