mysql - 使用 MySQL 从表中获取唯一数据-6ren

mysql - 使用 MySQL 从表中获取唯一数据

转载作者：行者123 更新时间：2023-11-29 05:05:11

我的目标是获取用户 A 与其其他 friend 的所有最后消息。

我有 table 友和留言

friend 表如下

+---------+-----------------+------+-----+---------+----------------+
| Field   | Type            | Null | Key | Default | Extra          |
+---------+-----------------+------+-----+---------+----------------+
| id      | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| user1   | int(6) unsigned | NO   | MUL | NULL    |                |
| user2   | int(6) unsigned | NO   | MUL | NULL    |                |
| pending | tinyint(1)      | NO   |     | NULL    |                |
+---------+-----------------+------+-----+---------+----------------+

消息表如下

+-----------------+-----------------+------+-----+-------------------+-----------------------------+
| Field           | Type            | Null | Key | Default           | Extra                       |
+-----------------+-----------------+------+-----+-------------------+-----------------------------+
| id              | int(6) unsigned | NO   | PRI | NULL              | auto_increment              |
| sender          | int(6) unsigned | NO   | MUL | NULL              |                             |
| receiver        | int(6) unsigned | NO   | MUL | NULL              |                             |
| message         | varchar(255)    | NO   |     | NULL              |                             |
| message_read    | tinyint(1)      | NO   |     | NULL              |                             |
| message_visible | int(6)          | YES  |     | NULL              |                             |
| message_date    | timestamp       | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-----------------+-----------------+------+-----+-------------------+-----------------------------+

这是个人资料表

+----------+-----------------+------+-----+---------+----------------+
| Field    | Type            | Null | Key | Default | Extra          |
+----------+-----------------+------+-----+---------+----------------+
| id       | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id  | int(6) unsigned | NO   | MUL | NULL    |                |
| nickname | varchar(50)     | YES  | UNI | NULL    |                |
| email    | varchar(50)     | YES  |     | NULL    |                |
| image    | mediumblob      | YES  |     | NULL    |                |
+----------+-----------------+------+-----+---------+----------------+

根据要求，这里是消息的数据集

+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+
| id | sender | receiver | message                                                     | message_read | message_visible | message_date        |
+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+
|  1 |      1 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
|  2 |      1 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:16 |
|  3 |      2 |        1 | test                                                        |            1 |            NULL | 2017-09-29 21:10:15 |
|  4 |      2 |        1 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
|  5 |      1 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
|  6 |      1 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
|  7 |      4 |        1 | test                                                        |            1 |            NULL | 2017-09-29 21:10:15 |
|  8 |      4 |        1 | test                                                        |            1 |            NULL | 2017-09-30 21:10:17 |
|  9 |      5 |        1 | test                                                        |            1 |            NULL | 2017-09-30 21:10:18 |
| 10 |      5 |        1 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 11 |      1 |        5 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
| 12 |      1 |        5 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 13 |      1 |        7 | test                                                        |            1 |               7 | 2017-09-30 21:10:15 |
| 14 |      2 |        3 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 15 |      2 |        3 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
| 16 |      3 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:17 |
| 17 |      3 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 18 |      3 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 19 |      3 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
| 20 |      4 |        3 | test                                                        |            1 |            NULL | 2017-09-30 21:10:17 |
| 21 |      4 |        3 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 22 |      2 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 23 |      2 |        4 | test                                                        |            1 |            NULL | 2017-09-30 21:10:14 |
| 24 |      4 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:17 |
| 25 |      4 |        2 | test                                                        |            1 |            NULL | 2017-09-30 21:10:15 |
| 26 |     11 |        4 | test1                                                       |            1 |            NULL | 2017-10-19 13:24:53 |
| 27 |     11 |        6 | test2                                                       |            1 |            NULL | 2017-10-19 19:56:46 |
| 28 |     11 |        7 | test3                                                       |            1 |            NULL | 2017-10-19 12:30:38 |
| 29 |      4 |       11 | test1_response                                              |            1 |            NULL | 2017-10-19 21:03:27 |
| 30 |      6 |       11 | test2_response                                              |            1 |            NULL | 2017-10-19 21:03:34 |
| 31 |     13 |        6 | test1                                                       |            1 |            NULL | 2017-10-20 09:36:09 |
| 32 |     13 |        7 | test1                                                       |            1 |            NULL | 2017-10-20 09:36:13 |
| 33 |     11 |       13 | test11                                                      |            1 |            NULL | 2017-10-20 09:36:21 |
| 34 |     13 |       11 | test11_response                                             |            1 |            NULL | 2017-10-20 09:41:27 |
| 35 |   1013 |        2 | test                                                        |            1 |            NULL | 2018-03-29 13:35:31 |
| 36 |      2 |     1013 | Longer text message test, and I wish this works beautifully |            1 |            NULL | 2018-03-29 15:12:33 |
| 37 |   1013 |        2 | Longer text message test, and I wish this works beautifully |            1 |            NULL | 2018-03-29 15:17:40 |
| 38 |   1013 |        2 | Longer text message test, and I wish this works beautifully |            1 |            NULL | 2018-03-29 15:41:55 |
| 39 |   1015 |        2 | What's up                                                   |            1 |            NULL | 2018-04-17 16:33:29 |
| 40 |      2 |     1015 | What up?                                                    |            1 |            NULL | 2018-04-17 16:33:29 |
| 42 |      2 |     1015 | What's up                                                   |            0 |            NULL | 2018-04-17 16:33:29 |
+----+--------+----------+-------------------------------------------------------------+--------------+-----------------+---------------------+

下面是 friend 的数据集

+-----+-------+-------+---------+
| id  | user1 | user2 | pending |
+-----+-------+-------+---------+
|   1 |     1 |     2 |       0 |
|   3 |     1 |     5 |       0 |
|   4 |     6 |     1 |       0 |
|   6 |     2 |     5 |       0 |
|   7 |     3 |     5 |       0 |
|   8 |     6 |     2 |       0 |
|   9 |     6 |     1 |       0 |
|  10 |     5 |     4 |       0 |
|  11 |     4 |     1 |       0 |
|  12 |     1 |     7 |       1 |
|  19 |    12 |     1 |       1 |
|  20 |    12 |     2 |       0 |
|  22 |    12 |     4 |       0 |
|  23 |    12 |    11 |       0 |
|  25 |     6 |    11 |       0 |
|  26 |     7 |    11 |       0 |
|  27 |     7 |    12 |       0 |
|  28 |     6 |    13 |       0 |
|  29 |     7 |    13 |       0 |
|  30 |    11 |    13 |       0 |
|  31 |  1013 |     2 |       0 |
|  39 |  1015 |     2 |       0 |
| 104 |  1015 |  1021 |       1 |
+-----+-------+-------+---------+

下面是个人资料数据片段

+------+---------+----------+-------+-------+
| id   | user_id | nickname | email | image |
+------+---------+----------+-------+-------+
|    1 |       1 | user1    |       | NULL  |
|    2 |       2 | user2    | NULL  | NULL  |
|    3 |       3 | user3    | NULL  | NULL  |
|    4 |       4 | user4    | NULL  | NULL  |
| 1014 |    1018 | user1019 | NULL  | NULL  |
| 1015 |    1019 | user1020 | NULL  | NULL  |
| 1016 |    1020 | user1021 | NULL  | NULL  |
| 1017 |    1021 | user1022 | NULL  | NULL  |
+------+---------+----------+-------+-------+

我根据 StackOverFlow 上发布的一些很棒的答案编写了我的声明

这是我的sql语句:

SELECT msg.message, msg.message_read, msg.message_date,   
CASE WHEN msg.receiver = 1013  
THEN (SELECT nickname FROM profile WHERE user_id = msg.sender) 
ELSE (SELECT nickname FROM profile WHERE user_id = msg.receiver) 
END AS name, 

CASE WHEN msg.receiver = 1013 
THEN (SELECT image FROM profile WHERE user_id = msg.sender)  
ELSE (SELECT image FROM profile WHERE user_id = msg.receiver)  
END AS image  

FROM message msg  
JOIN  
(SELECT user, max(message_date) m  
FROM  
((SELECT id, receiver user, message_date FROM message WHERE sender = 1015 AND (message_visible =1 OR message_visible IS NULL))  
UNION  
(SELECT id, sender user, message_date FROM message WHERE receiver = 1015 AND (message_visible =1 OR message_visible IS NULL))) m1 GROUP BY user) m2 ON  
((sender = 1015 AND receiver = user) OR (sender = user AND receiver = 1015)) AND (message_date = m) ORDER BY message_date desc;

我得到的结果是

+-----------+--------------+---------------------+----------+-------+
| message   | message_read | message_date        | name     | image |
+-----------+--------------+---------------------+----------+-------+
| What's up |            1 | 2018-04-17 16:33:29 | user2    | NULL  |
| What up?  |            1 | 2018-04-17 16:33:29 | user1016 | NULL  |
| What's up |            0 | 2018-04-17 16:33:29 | user1016 | NULL  |
+-----------+--------------+---------------------+----------+-------+

我只想得到其中一个。哪个并不重要。当然，如果有更多的用户，就会有更多的消息。问题是当我有相同的 message_date 时。虽然同时发送消息似乎不太可信，但我至少想学习如何控制这种情况。

最佳答案

首先，您从您的用户 A 那里获得与其他任何人的消息。我没有包含消息何时不可见的条件，因为问题中的逻辑不明确。您必须自己添加。

SQL DEMO (没有配置文件表)

SELECT m.message, 
       m.message_read,
       m.message_date,
       CASE WHEN m.sender = @userA_ID
            THEN m.receiver
            ELSE m.sender
       END as friend_id,           
       CASE WHEN m.sender = @userA_ID
            THEN p2.nickname
            ELSE p1.nickname
       END as name,
       CASE WHEN m.sender = @userA_ID
            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 = p1.user_id  -- receiver
WHERE @userA_ID IN (m.sender, m.receiver)

现在您使用变量按 friend 和日期对消息进行排序，请注意您使用 friend_id 以防两个 friend 同名。

SELECT t.*,
       @rn := if(@friend = t.friend_id, 
                 @rn + 1,
                 if( @friend := t.friend_id, 1, 1)
                ) as rn
FROM ( ** previous_query ** ) as t
CROSS JOIN ( SELECT @rn := 0, @friend := 0 ) as var
ORDER BY t.friend_id, t.message_date desc

请注意，您可以添加另一个条件来处理相同日期的关系

最后，您使用 rn = 1

过滤消息

SELECT *
FROM ( ** second query **) q
WHERE q.rn = 1

关于mysql - 使用 MySQL 从表中获取唯一数据，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/49890897/

文章推荐： php - 如何对使用 with() 返回的行进行分页？

文章推荐： C# Mysql cultureinfo.invariantculture

文章推荐： php - 查询错误，但我不知道它在哪里

文章推荐： ios - 将所选值放入文本字段中

c# - 异步任务获取 VS HttpResponseMessage 获取
我需要您在以下方面提供帮助。近一个月来，我一直在阅读有关任务和异步的内容。我想尝试在一个简单的 wep api 项目中实现我新获得的知识。我有以下方法，并且它们都按预期工作: public Htt
java - 无法从 URL 获取 URI，获取 null？
我的可执行 jar 中有一个模板文件 (.xls)。不需要在运行时我需要为这个文件创建 100 多个副本(稍后将唯一地附加)。用于获取 jar 文件中的资源 (template.xls)。我正在使用
javascript - Backbone 的模型原型(prototype)获取 vs backbone 获取
我在查看网站的模型代码时对原型(prototype)有疑问。我知道这对 Javascript 中的继承很有用。在这个例子中... define([], function () { "use
javascript - 获取 scrollTop、获取 offsetHeight 和 getStyle 需要很长时间
影响我性能的前三项操作是: 获取滚动条获取偏移高度 Ext.getStyle 为了解释我的应用程序中发生了什么:我有一个网格，其中有一列在每个单元格中呈现网格。当我几乎对网格的内容做任何事情时，它运
javascript - 获取 URL 参数函数，获取 url 部分的值，或者如果存在但没有值则返回 true？
我正在使用以下函数来获取 URL 参数。 function gup(name, url) { name = name.replace(/[\[]/, '\\\[').replace(/[\]]/,
c - MacOS 使用 sysctl() 获取 HW_MACHINE_ARCH 获取 "no such file or directory"
我最近一直在使用 sysctl 来做很多事情，现在我使用 HW_MACHINE_ARCH 变量。我正在使用以下代码。请注意，当我尝试获取其他变量 HW_MACHINE 时，此代码可以完美运行。我还认为
ios - 将我的 YouTube channel 获取(获取)到我的 iOS 应用程序中
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。关闭 9 年前。要求提供代码的问题必须表现出对所解决问题的最低限度的理解。包括尝试过的解决方案、为什么
javascript - webpack:如何从 "bower_components"获取 JavaScript，而不是从 "node_modules"获取 JavaScript
由于使用 main-bower-files 作为使用 Gulp 的编译任务的一部分，我无法使用 node_modules 中的 webpack 来require 模块code> dir 因为我会弄乱当
Javascript - 从 "Monday"获取 "mon"或从 "Tuesday"获取 "tue"等
关闭。这个问题需要更多focused .它目前不接受答案。想改进这个问题吗？更新问题，使其只关注一个问题 editing this post . 关闭 5 年前。 Improve this qu
Java:无法将 Gridlayout 应用于 Jscrollpane。获取获取 java.lang.ClassCastException
我使用 Gridlayout 在一行中放置 4 个元素。首先，我有一个 JPanel，一切正常。对于行数变大并且我必须能够向下滚动的情况，我对其进行了一些更改。现在我的 JPanel 上添加了一个 J
python - 如何从 key BlockDeviceMappings 获取 VolumeId(boto3 获取 ec2 的卷信息)
由于以下原因，我想将 VolumeId 的值保存在变量中: #!/usr/bin/env python import boto3 import json import argparse import
angularjs - 未使用 acquireTokensilent 获取 token ，但使用 acquireTokenpopup Msal-browser 获取 token
我正在将 MSAL 版本 1.x 更新为 MSAL-browser 的 Angular 。所以我正在尝试从版本 1.x 迁移到 2.X.I 能够成功替换代码并且工作正常。但是我遇到了 acquireT
python - 使用 GroupBy 获取 Pandas 的平均值 - 获取 DataError : No numeric types to aggregate -
我知道有很多关于此的问题，例如 Getting daily averages with pandas和 How get monthly mean in pandas using groupby但我遇到
javascript - 无法在 mvc 获取 Controller 方法(来自 Uri())中从 QueryString 获取 DATETIME
This is the query string that I am receiving in URL. Output url: /demo/analysis/test?startDate=Sat+
ubuntu - 从 OpenLayer 3 获取 Geoserver 获取 '500 (Internal Server Error)'
我正在尝试使用 javascript 中的以下代码访问 Geoserver 层 var gkvrtWmsSource =new ol.source.ImageWMS({ u
javascript - 使用 XMLHttpRequest 获取 Ecobee API 信息。获取 500(错误 1 : "Authentication failed. Token is required.")
API 需要一个包含授权代码的 header 。这就是我到目前为止所拥有的: var fullUrl = 'https://api.ecobee.com/1/thermostat?json=\{"s
c# - 获取/删除文件的最后一个字符而不加载到内存中
如何获取文件中的最后一个字符，如果是某个字符，则删除它而不将整个文件加载到内存中？这就是我目前所拥有的。 using (var fileStream = new FileStream("file.t
JSP 获取/设置整个对象的参数
我是这个社区的新手，想出了我的第一个问题。我正在使用 JSP，我成功地创建了 JSP-Sites，它正在使用jsp:setParameter 和 jsp:getParameter 具有单个字符串。
multithreading - 获取/释放语义
在回答 StoreStore reordering happens when compiling C++ for x86 @Peter Cordes 写过 For Acquire/Release se
javascript - 获取 .on 中使用的函数的结果
我有一个函数，我们将其命名为 X1，它返回变量 Y。该函数在操作 .on("focusout", X1) 中使用。如何获取变量Y？执行.on后X1的结果？最佳答案您可以更改 Y 的范围以使其位于函

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - 使用 MySQL 从表中获取唯一数据