从mysql获取唯一数据

px9o7tmv 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(356)

我正在尝试获取最新消息的列表。情况是这样的：当有消息以完全相同的时间发送时，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 不考虑“完全相同的消息日期”。很可能不可能同时发送消息，但谁知道呢？
朋友表：

+----+-------+-------+---------+--------------+
ø 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 ø
+----+--------+----------+-----------+--------------+-----------------+---------------------+

如果你知道如何解决这个问题就太好了。当我运行上面的sequel语句时，得到的结果如下。

+-----------+--------------+---------------------+-----------+----------------------+-------+------+
ø 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 ø
+-----------+--------------+---------------------+-----------+----------------------+-------+------+

如您所见，有两条来自同一个朋友的消息，即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 |
+-----------+--------------+---------------------+-----------+-------+-------+------+

mysql Database select

来源：https://stackoverflow.com/questions/50353371/getting-unique-data-from-mysql

1条答案

按热度按时间

6yjfywim1#

如果您将一个id添加到结果表中，那么您可以选择最新的id并按friend\u 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表的要求获得结果应该是相当直接的，因为您已经根据您的问题做了类似的操作。

赞(0）回复(0）举报 2021-06-21

我来回答

从mysql获取唯一数据

1条答案

相关问题

热门标签

最新问答