MySQL：有没有一种方法可以从结果中消除重复的条目

Question

我有一个名为“message”的表，用于存储一个用户发送给另一个用户的消息。我想创建一个消息盒子，其中包含特定用户的收件和发件消息。这个消息盒子应该包含两个用户之间的最后一条消息。因此，我必须消除两个用户之间的重复消息。我尝试使用group by来消除重复消息，但是我无法选择最新的消息，因为order by在group by之后工作。我尝试使用distinct函数来消除重复消息。它运行良好，但我必须选择所有列，这在distinct中不可能实现。

我的消息表：

+-------+---------+------+-----+------------+
| id    | from_id | to_id| text| created_at |
+-------+---------+------+-----+------------+
| 1     | 1       | 2    | mes | 2014-01-16 |
| 2     | 2       | 1    | mes | 2014-01-17 |
| 3     | 1       | 3    | mes | 2014-01-18 | 
| 4     | 3       | 1    | mes | 2014-01-19 |
+-------+---------+------+-----+------------+

我的SQL中的"Group By"语句

SELECT * FROM message WHERE (from_id = 1 OR to_id = 1) GROUP BY(from_id + to_id) ORDER BY created_at DESC;

关键字 Distinct

SELECT DISTINCT(from_id + to_id) FROM message WHERE (from_id = 1 OR to_id = 1)

在上面的例子中，我想选择第二个和第四个消息。

有没有一种方法可以在结果中消除两个用户之间的重复消息？

编辑：我已经改进了示例。

- Can Aydoğan

---

你为什么要尝试添加这些ID？ - Mike Brant

你的示例表中没有重复的消息。如果一个消息具有相同的 from_id 和 created_at，则应视为重复消息。如果满足这些条件，则按created_at分组将只选择其中一个消息，主要是满足_from_id_和_created_at_两个值都相同的条件。 - digitai

你误用了GROUP BY。除非你选择使用像MIN()或MAX()这样的聚合函数，否则它没有任何意义。 - O. Jones

@datelligent 我已经更新了我的问题，以便更加清晰明了。 - Can Aydoğan

@CanAydoğan，为什么不根据消息的性质将其分类为“入站”和“出站”，这样更容易选择最后一条消息，或者按发送者/接收者和日期计算消息数量。 - digitai

@datelligent 我希望用户可以在一个列表中看到他们所有的消息，就像Facebook一样。我认为这种方法比将消息分为收件箱和发件箱更有用。 - Can Aydoğan

Answer 1

如果我正确理解您想要实现的内容，您可以利用MySQL中的LEAST()、GREATEST()函数和非标准的GROUP BY扩展行为来实现。

SELECT id, from_id, to_id, text, created_at
  FROM
(
  SELECT id, from_id, to_id, text, created_at 
    FROM message
   ORDER BY LEAST(from_id, to_id), GREATEST(from_id, to_id), created_at DESC
) q
 GROUP BY LEAST(from_id, to_id), GREATEST(from_id, to_id)

这将为每对用户提供最后一条消息行。

输出:

+------+---------+-------+------+------------+
| id   | from_id | to_id | text | created_at |
+------+---------+-------+------+------------+
|    2 |       2 |     1 | mes  | 2014-01-17 |
|    4 |       3 |     1 | mes  | 2014-01-19 |
+------+---------+-------+------+------------+

这里有一个 SQLFiddle 演示

- peterm

Answer 2

我尝试使用group by，它可以消除重复的消息，但我无法选择最近的消息，因为order by在group by之后执行。因此，您可以在group by之前对其进行排序：

SELECT * 
FROM (SELECT * FROM message ORDER BY created_at DESC)
WHERE (from_id = 1 OR to_id = 1) GROUP BY(from_id + to_id);

- carexcer

---

它运行得很好。但我想知道像这样使用“group by”是否存在违规或问题。 - Can Aydoğan

Answer 3

尝试在 GROUP BY 之后添加 HAVING 子句：HAVING COUNT(*) > 1

或者

SELECT 列名, COUNT(*) FROM (SELECT DISTINCT 列名 FROM 消息表

    ) 
    message

按列名分组

筛选出 COUNT(*) 大于 1 的结果

- user2821300

Answer 4

您可以使用：

ORDER BY id DESC LIMIT 1

或者按时间戳（假设它包含日期和时间）：

ORDER BY create_at DESC LIMIT 1

这将按降序对所有结果进行排序，并仅提供最后一行。

希望这可以帮到你！

- dev7

Answer 5

只需使用简单的select语句。没有理由创建重复的内容。

SELECT from_id, to_id, text, created_at
FROM message
WHERE
(from_id = ? AND to_id = ??)
OR (from_id = ?? AND to_id = ?)

这里的?代表一个id，而??代表另一个。

这里不会有重复项。可以通过几种方式实现排序：

按最近的消息排序，不考虑发送者：

SELECT from_id, to_id, text, created_at
FROM message
WHERE
(from_id = ? AND to_id = ??)
OR (from_id = ?? AND to_id = ?)
ORDER BY created_at DESC

首先按发送者消息排序（然后按创建时间排序）

SELECT from_id, to_id, text, created_at
FROM message
WHERE
(from_id = ? AND to_id = ??)
OR (from_id = ?? AND to_id = ?)
ORDER BY from_id = ? DESC, created_at DESC

- Mike Brant

Answer 6

SQL Fiddle

MySQL 5.5.32架构设置:

CREATE TABLE message
    (`id` int, `from_id` int, `to_id` int, `text` varchar(3), `created_at` datetime)
;

INSERT INTO message
    (`id`, `from_id`, `to_id`, `text`, `created_at`)
VALUES
    (1, 1, 2, 'mes', '2014-01-16 00:00:00'),
    (2, 2, 1, 'MUL', '2014-01-17 00:00:00')
;

查询 1:

SELECT * 
FROM message 
WHERE from_id = 1 OR to_id = 1
ORDER BY created_at DESC
limit 1

结果:

| ID | FROM_ID | TO_ID | TEXT |                     CREATED_AT |
|----|---------|-------|------|--------------------------------|
|  2 |       2 |     1 |  MUL | January, 17 2014 00:00:00+0000 |

- D'Arcy Rittich