时间戳

首页 > 解决方案 > 需要使用 group by 和 having 子句验证一致的结果集

sql - 需要使用 group by 和 having 子句验证一致的结果集

问题描述

我有一张如下表

CREATE TABLE `zpost` (
  `post_id` int(10) UNSIGNED NOT NULL,
  `topic_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `post_subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

数据集为

INSERT INTO `zpost` (`post_id`, `topic_id`, `post_subject`) VALUES
(44, 33, 'New topic by new user'),
(45, 33, 'Re: New topic by new user'),
(47, 33, 'Re: New topic by new user'),
(46, 34, 'New Topic by James on 1/2'),
(48, 35, 'Sep 29th new topic'),
(49, 35, 'Re: Sep 29th new topic'),
(50, 35, 'Re: Sep 29th new topic'),
(51, 36, 'Another Sep topic');

和索引（与问题无关，但在这里）

ALTER TABLE `zpost`
  ADD PRIMARY KEY (`post_id`),
  ADD KEY `topic_id` (`topic_id`);

最后是 SQL

SELECT * FROM `zpost` group by `topic_id` having min(`topic_id`);

最后输出

|post_id|topic_id|post_subject              |
+-------+--------+--------------------------+
|     44|      33|New topic by new user     |
|     46|      34|New Topic by James on 1/2 |
|     48|      35|Sep 29th new topic        |
|     51|      36|Another Sep topic         |

我只想要给定 topic_id 的最小 post_id - 第一个主题记录。我似乎默认情况下得到了这一点。不确定这是否只是数据库决定提供行的方式，或者这是否是一致的顺序。有问题的数据库是 MariaDB。我还尝试在数据库中以相反的顺序插入数据，如下所示

INSERT INTO `zpost` (`post_id`, `topic_id`, `post_subject`) VALUES
(51, 36, 'Another Sep topic'),
(50, 35, 'Re: Sep 29th new topic'),
(49, 35, 'Re: Sep 29th new topic'),
(48, 35, 'Sep 29th new topic'),
(46, 34, 'New Topic by James on 1/2'),
(47, 33, 'Re: New topic by new user'),
(45, 33, 'Re: New topic by new user'),
(44, 33, 'New topic by new user');

而且我仍然得到了我想要的结果，这是个好消息，不需要采取进一步的行动。但不确定为什么以及为了完整起见，如果我想要最后一行（最大 post_id），我将如何更改 SQL 以使该行与每个 topic_id 关联？有人会认为将 min 更改为 max 会解决这个问题，但不是！对于这个查询，我也得到了相同的结果。

SELECT * FROM `zpost` group by `topic_id` having max(`topic_id`);

标签： sqlgroup-bymariadbhaving-clause

解决方案

首先，在关系数据库中，表中的行没有任何固有的顺序。插入、更新或删除它们的顺序无关紧要。表格代表无序的行包。

您可以使用ROW_NUMBER()来标识您想要的行。

post_id要让每个人都变老，topic_id你可以这样做：

select post_id, topic_id, post_subject
from (
  select *, row_number() over(partition by topic_id order by post_id) as rn
  from zpost
) x
where rn = 1

结果：

 post_id  topic_id  post_subject              
 -------- --------- ------------------------- 
 44       33        New topic by new user     
 46       34        New Topic by James on 1/2 
 48       35        Sep 29th new topic        
 51       36        Another Sep topic

请参阅DB Fiddle-ASC上的运行示例。

要获得post_id每个人的最新信息，topic_id您可以执行以下操作：

select post_id, topic_id, post_subject
from (
  select *, row_number() over(partition by topic_id order by post_id desc) as rn
  from zpost
) x
where rn = 1

结果：

 post_id  topic_id  post_subject              
 -------- --------- ------------------------- 
 47       33        Re: New topic by new user 
 46       34        New Topic by James on 1/2 
 50       35        Re: Sep 29th new topic    
 51       36        Another Sep topic

请参阅DB Fiddle-DESC上的运行示例。

推荐阅读