时间戳

首页 > 解决方案 > 从 MySQL 获取唯一数据

mysql - 从 MySQL 获取唯一数据

问题描述

我正在尝试获取最新消息的列表。情况如下：当有完全相同时间发送的消息时，sequel 语句会选择所有消息。我只需要来自每个聊天室的一条消息，即最新的一条消息。在@JuanCarlosOropeza 的帮助下（以及他精湛的小提琴http://sqlfiddle.com/#!9/d506e/10，我将立即将其包含在我曾经就这个主题提出的每个问题中）我得到了下面的续集声明。

SELECT *
FROM (  SELECT t.*,
               @rn := if(@friend = t.friend_id, 
                         @rn + 1,
                         if( @friend := t.friend_id, 1, 1)
                        ) as rn
        FROM ( 
                SELECT m.message, 
                       m.message_read,
                       m.message_date,
                       CASE WHEN m.sender = 4
                            THEN m.receiver
                            ELSE m.sender
                       END as friend_id,
                       CASE WHEN m.sender = 4
                            THEN p2.nickname
                            ELSE p1.nickname
                       END as name,
                       CASE WHEN m.sender = 4
                            THEN p2.image
                            ELSE p1.image
                       END as image
                FROM message as m
                JOIN profile as p1
                  ON m.sender = p1.user_id    -- sender
                JOIN profile as p2 
                  ON m.receiver = p2.user_id  -- receiver
                WHERE 4 IN (m.sender, m.receiver)
        ) as t
        CROSS JOIN ( SELECT @rn := 0, @friend := 0 ) as var
        ORDER BY t.friend_id, t.message_date desc
     ) q
WHERE q.rn = 1

上面的续集声明做得很好；但是，它有一个缺陷。ORDER BY t.friend_id, t.message_date desc不考虑“完全相同的 message_date”。同时发送消息很可能是不可能的，但谁知道呢？

好友表：

+----+-------+-------+---------+--------------+
ø id ø user1 ø user2 ø pending ø request_date ø
+----+-------+-------+---------+--------------+
ø  1 ø     4 ø     2 ø       0 ø 2018-05-09   ø
ø  2 ø     5 ø     2 ø       0 ø 2018-05-09   ø
ø  3 ø     1 ø     4 ø       0 ø 2018-05-09   ø
+----+-------+-------+---------+--------------+

简介表：

+----+---------+----------------------+---------------+-------+
ø id ø user_id ø nickname             ø email         ø image ø
+----+---------+----------------------+---------------+-------+
ø  1 ø       1 ø Welcome to MathMatch ø test@test.com ø NULL  ø
ø  2 ø       2 ø user3                ø NULL          ø NULL  ø
ø  3 ø       3 ø USER4                ø NULL          ø NULL  ø
ø  4 ø       4 ø elokiller            ø NULL          ø NULL  ø
ø  5 ø       5 ø USER6                ø NULL          ø NULL  ø
+----+---------+----------------------+---------------+-------+

留言表：

+----+--------+----------+-----------+--------------+-----------------+---------------------+
ø id ø sender ø receiver ø message   ø message_read ø message_visible ø message_date        ø
+----+--------+----------+-----------+--------------+-----------------+---------------------+
ø  1 ø      4 ø        2 ø lulz      ø            1 ø               2 ø 2018-05-15 10:24:36 ø
ø  2 ø      1 ø        4 ø Hey Buddy ø            1 ø            NULL ø 2018-05-10 11:58:39 ø
ø  3 ø      2 ø        4 ø nooo      ø            1 ø               2 ø 2018-05-15 10:24:36 ø
ø  4 ø      4 ø        2 ø shut up   ø            1 ø               2 ø 2018-05-15 10:24:36 ø
+----+--------+----------+-----------+--------------+-----------------+---------------------+

如果您知道如何解决此问题，那就太好了。当我运行上述续集语句时，我得到的结果如下。

+-----------+--------------+---------------------+-----------+----------------------+-------+------+
ø message   ø message_read ø message_date        ø friend_id ø name                 ø image ø rn   ø
+-----------+--------------+---------------------+-----------+----------------------+-------+------+
ø Hey Buddy ø            1 ø 2018-05-10 11:58:39 ø         1 ø Welcome to MathMatch ø NULL  ø    1 ø
ø lulz      ø            1 ø 2018-05-15 10:24:36 ø         2 ø user3                ø NULL  ø    1 ø
ø nooo      ø            1 ø 2018-05-15 10:24:36 ø         2 ø user3                ø NULL  ø    1 ø
+-----------+--------------+---------------------+-----------+----------------------+-------+------+

我期望的结果：

+-----------+--------------+---------------------+-----------+----------------------+-------+------+
ø message   ø message_read ø message_date        ø friend_id ø name                 ø image ø rn   ø
+-----------+--------------+---------------------+-----------+----------------------+-------+------+
ø Hey Buddy ø            1 ø 2018-05-10 11:58:39 ø         1 ø Welcome to MathMatch ø NULL  ø    1 ø
ø lulz      ø            1 ø 2018-05-15 10:24:36 ø         2 ø user3                ø NULL  ø    1 ø
+-----------+--------------+---------------------+-----------+----------------------+-------+------+

如您所见，有两条来自同一个friend_id 的消息，即2。我只想从那个朋友那里检索一条消息。感谢您抽出宝贵的时间。任何评论都会有所帮助~

另一个需要关注的情况。

当前消息表：

+----+--------+----------+-----------+--------------+-----------------+---------------------+
| id | sender | receiver | message   | message_read | message_visible | message_date        |
+----+--------+----------+-----------+--------------+-----------------+---------------------+
|  1 |      4 |        2 | lulz      |            1 |               2 | 2018-05-15 10:24:36 |
|  2 |      1 |        4 | Hey Buddy |            1 |            NULL | 2018-05-10 11:58:39 |
|  3 |      2 |        4 | nooo      |            1 |               2 | 2018-05-15 10:24:36 |
|  4 |      4 |        2 | shut up   |            1 |               2 | 2018-05-15 10:24:36 |
|  5 |      4 |        2 | heha      |            1 |            NULL | 2018-05-15 10:36:11 |
|  6 |      1 |        4 | SUP MATE  |            1 |            NULL | 2018-05-15 11:04:24 |
+----+--------+----------+-----------+--------------+-----------------+---------------------+

当前结果表：

+-----------+--------------+---------------------+-----------+-------+-------+------+
| message   | message_read | message_date        | friend_id | name  | image | rn   |
+-----------+--------------+---------------------+-----------+-------+-------+------+
| Hey Buddy |            1 | 2018-05-10 11:58:39 |         1 | JUAN  | NULL  |    1 |
| nooo      |            1 | 2018-05-15 10:24:36 |         2 | user3 | NULL  |    1 |
+-----------+--------------+---------------------+-----------+-------+-------+------+

期待结果

+-----------+--------------+---------------------+-----------+-------+-------+------+
| message   | message_read | message_date        | friend_id | name  | image | rn   |
+-----------+--------------+---------------------+-----------+-------+-------+------+
| SUP MATE  |            1 | 2018-05-15 11:04:24 |         1 | JUAN  | NULL  |    1 |
| heha      |            1 | 2018-05-15 10:36:11 |         2 | user3 | NULL  |    1 |
+-----------+--------------+---------------------+-----------+-------+-------+------+

标签： mysqldatabaseselect

解决方案

如果您在结果表中添加一个 ID，那么您可以选择最新的 id 并按friend_id 分组。

假设您能够构建以下类型的视图

CREATE TABLE IF NOT EXISTS `MESSAGES` (
  `id` int(6) unsigned NOT NULL,
  `friend_id` int(3) unsigned NOT NULL,
  `message_time` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `MESSAGES` (`id`, `friend_id`, `message_time`) VALUES
  ('1', '1', '2018-05-10 11:58:39'),
  ('2', '2', '2018-05-15 10:24:36'),
  ('3', '2', '2018-05-15 10:24:36');

然后，您将首先选择按朋友 ID 分组的最大时间的消息，然后您将选择其中最大时间的消息id。您可以使用以下 SQL 来实现。

SELECT MESSAGES.id, MESSAGES.friend_id, MESSAGES.message_time FROM

(
  SELECT max(id) as id, friend_id FROM
  (SELECT id,
          m.friend_id,
          m.message_time
   FROM MESSAGES m
   JOIN
     (SELECT friend_id,
             MAX(message_time) AS message_time
      FROM MESSAGES
      GROUP BY friend_id) mx ON mx.friend_id = m.friend_id
   AND mx.message_time = m.message_time) b GROUP BY friend_id
) a JOIN 
   MESSAGES on a.id = MESSAGES.id ;

在上述查询中获取 MESSAGES 表所要求的结果应该相当简单，因为您根据您的问题做了类似的事情。

推荐阅读