从每个分组中获取最新的记录

Question

我希望能够对ejabberd的存档表进行翻译，以对话双方为依据，整理出特定用户与每位用户之间的最后一条消息。以下是我使用的字段：

- id（消息id） - username（用户名副本） - bare_peer（正在聊天的用户） - txt（文本聊天） - created_at（创建时间）

我的目标是按照bare_peer分组，其中用户名1_usernode，只留下最后一条消息。尽管我已经尝试过多个查询语句，但都未成功。以下是我尝试的第一个查询语句。

SELECT id, username, bare_peer, txt FROM archive where
username = '1_usernode' GROUP BY bare_peer ORDER BY created_at DESC;

这是输出结果。

+------+------------+-------------------------------------------------------+---------------------+
| id   | username   | bare_peer              | txt                          | created_at          |
+------+------------+------------------------+------------------------------+---------------------+
| 1095 | 1_usernode | 10_usernode@localhost  | Hello !!!                    | 2016-07-17 21:15:17 |
| 1034 | 1_usernode | 15_usernode@localhost  | hey sup ?                    | 2016-07-13 22:40:29 |
| 1107 | 1_usernode | 13_usernode@localhost  | oi                           | 2016-07-18 00:09:28 |
| 1078 | 1_usernode | 2_usernode@localhost   | Hello this is just a Test!!! | 2016-07-15 16:30:50 |
| 1101 | 1_usernode | 7_usernode@localhost   | hey                          | 2016-07-18 00:05:55 |
| 1084 | 1_usernode | 3_usernode@localhost   | Hey how are you?             | 2016-07-15 19:36:44 |
| 1085 | 1_usernode | 4_usernode@localhost   | Hey how are you doing ?      | 2016-07-17 19:20:00 |

- RenatoCv

---

如果您展示输入数据和期望的输出，会更有帮助。也许可以创建一个 SQL Fiddle。 - PaulF

输出是正确的，但它给我最旧的消息，我需要的是最新的。 - RenatoCv

你不能使用 GROUP BY 来实现你的目标，因为 GROUP BY 执行的是另一种操作。它不会返回数据库中的行，而是使用从数据库中提取的数据生成新的行。你发布的查询语句不是有效的 SQL 语句。两个（或更多）具有相同 bare_peer 值的不同行将落入同一组中。它们具有不同的 id 值，但你想要 SELECT id。哪个 id？ - axiac

created_at 被设置为时间戳 (current_timestamp)。 - RenatoCv

看一下这个答案。它解决了同样的问题。 - axiac

Answer 1

我想知道为什么在不选择created_at的情况下它会显示created_at列？而且我不知道你为什么要使用group by？没有任何需要分组的东西。

我的语句看起来像这样：select id, username, bare_peer, txt, created_at from archive where username = '1_usercode' order by created_at desc

- A.Flash

---

我不小心从原始查询中删除了created_at。 - RenatoCv

Answer 2

尝试这个查询：

SELECT archive.id, archive.max_id, archive.username, archive.bare_peer, archive.txt 
FROM archive join 
(SELECT MAX(id) max_id, username, bare_peer, txt 
FROM archivewhere username = '1_usernode' GROUP BY bare_peer)
tab on archive.id=tab.max_id

- Abhishek Sharma

---

它返回给我最旧的消息，但无论如何还是谢谢！ - RenatoCv

尝试运行以下查询：SELECT * FROM archive join (SELECT MAX(id) max_id, username, bare_peer, txt FROM archive where username = '1_usernode' GROUP BY bare_peer) tab on archive.id=tab.max_id @RenatoCv - Abhishek Sharma

在同一查询中使用 SELECT * 和 GROUP BY 是无效的 SQL。 - axiac

输出返回最新消息的时间和ID，与旧消息的文本内容正确组合。它几乎可以工作了。 - RenatoCv

SELECT archive.id, archive.max_id, archive.username, archive.bare_peer, archive.txt FROM archive join (SELECT MAX(id) max_id, username, bare_peer, txt FROM archive where username = '1_usernode' GROUP BY bare_peer) tab on archive.id=tab.max_id 请尝试使用此查询 @RenatoCv - Abhishek Sharma

Answer 3

使用此查询非常有帮助。

SELECT MAX(id), username, bare_peer, txt FROM archive where
username = '1_usernode' ORDER BY created_at DESC

- Hardik Boghara

---

由于隐式分组（使用聚合函数但没有 GROUP BY 子句），此查询返回单行数据，使 ORDER BY 子句无效（无论如何查询都无法返回预期结果）。 - axiac

Answer 4

声明 created_at 为日期时间类型

试一下这个

DROP TABLE IF EXISTS `archive`;

CREATE TABLE `archive` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) DEFAULT NULL,
  `bare_peer` VARCHAR(50) DEFAULT NULL,
  `txt` TEXT,
  `created_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB  DEFAULT CHARSET=latin1;

/*archive表的数据*/

INSERT  INTO `archive`(`id`,`username`,`bare_peer`,`txt`,`created_at`) 
VALUES (1034,'1_usernode','15_usernode@localhost','hey sup ?','2016-07-13 22:40:29'),
(1078,'1_usernode','2_usernode@localhost','Hello this IS just a Test!!!','2016-07-15 16:30:50'),
(1084,'1_usernode','3_usernode@localhost','Hey how are you?','2016-07-15 19:36:44'),
(1085,'1_usernode','4_usernode@localhost','Hey how are you doing ?','2016-07-17 19:20:00'),
(1095,'1_usernode','10_usernode@localhost','Hello !!!','2016-07-17 21:15:17'),
(1101,'1_usernode','7_usernode@localhost','hey','2016-07-18 00:05:55'),
(1107,'1_usernode','13_usernode@localhost','oi','2016-07-18 00:09:28');

然后运行您的查询

SELECT id, username, bare_peer, txt FROM archive where
username = '1_usernode' GROUP BY bare_peer ORDER BY created_at DESC;

- Rafiqul Islam

---

如果我只修改相关的列，它会正常工作吗？ - RenatoCv

我创建了一个名为archive2的第二个表格，并添加了更多早期日期的数据，但它总是返回最旧的消息。 - RenatoCv

Answer 5

尝试以下代码：-

select m.*
from
messages m
inner join (
select max(id) as maxid
from messages
group By (if(username > bare_peer,  username, bare_peer)), 
(if(username > bare_peer,  bare_peer, username))
) t1 on m.id=t1.maxid ;

m是消息表的别名

- kunal

Answer 6

你想要每个用户名和bare_peer的最大created_at条目。在MySQL中，一种方法是使用'having'，但我不喜欢那样做。我会先获取每个条目的max(created_at)：

select username, bare_peer, max(created_at) as m_
from archive
group by username, bare_peer;

然后在该结果上加入表：

select b.*
from (
    select username, bare_peer, max(created_at) as m_
    from archive
    group by username, bare_peer
) a
inner join archive as b on (
    a.username = b.username
    and a.bare_peer = b.bare_peer
    and a.m_ = b.created_at
)

- verhie

Answer 7

我使用Rahauto的答案创建了一个临时解决方案。 我将他的查询放在子查询中，以便从其ID中提取消息内容。

SELECT username, bare_peer, txt, created_at FROM archive WHERE id IN (
  SELECT tab.max_id  FROM
    archive JOIN (SELECT MAX(id) max_id, username, bare_peer, txt FROM
    archive WHERE username = '1_usernode' GROUP BY bare_peer)
  tab ON archive.id=tab.max_id
);

- RenatoCv