首页>问答>mysql sql查询如何实现发私信用户和其他用户的列表？要求消重所有重复的用户结果

最代码官方 2014-10-27 17:55:18

完

mysql sql查询如何实现发私信用户和其他用户的列表？要求消重所有重复的用户结果

私信表结构如下：

建表语句和数据

01CREATE TABLE `message` (
02  `id` int(5) NOT NULL,
03  `sender_id` int(5) DEFAULT NULL,
04  `receivce_id` int(5) DEFAULT NULL,
05  `content` varchar(255) DEFAULT NULL,
06  PRIMARY KEY (`id`)
07) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
08 
09 
10 
11/*Data for the table `message` */
12 
13 
14 
15insert  into `message`(`id`,`sender_id`,`receivce_id`,`content`) values (1,1,2,'1发给2'),(2,2,1,'2发给1'),(3,3,1,'3发给1'),(4,2,1,'2发给1'),(5,1,2,'1发给2'),(6,4,1,'4发给1'),(7,1,2,'1-2'),(8,2,1,'2-1'),(9,2,1,'2-1'),(10,1,2,'1-2'),(11,3,1,'3-1'),(12,3,2,'3-2'),(13,3,1,'3-1'),(14,4,1,'4-1'),(15,5,1,'5-1'),(16,1,5,'1-5'),(17,1,5,'1-5'),(18,1,6,'1-6'),(19,6,1,'6-1'),(20,1,7,'1-7'),(21,1,8,'1-8'),(22,1,9,NULL),(23,1,10,NULL),(24,11,1,NULL),(25,12,1,'12-1'),(26,13,1,'13-1'),(27,1,14,NULL),(28,15,1,'15-1');

要求得到1和所有用户的私信列表。

结果集如下：

id sender_id receivce_id content

1 1 2 1发给2

3 3 1 3发给1

6 4 1 4发给1

6个评论

5个牛币 mysql sql 查询私信用户

所有回答列表(7)

woniu LV8 2014年10月30日

SELECT * FROM (
SELECT id, receivce_id,content,
CASE WHEN sender_id=1
THEN "发送方"
ELSE "接收方" END flag
FROM message WHERE sender_id=1
UNION
SELECT id,sender_id,content,
CASE WHEN sender_id=1
THEN "发送方"
ELSE "接收方" END flag
FROM message WHERE receivce_id=1
ORDER BY receivce_id DESC,id DESC )ta
GROUP BY receivce_id
ORDER BY id DESC

思路：

1.通过 UNION 语句合并 receivce_id 和sender_id

2.根据 receivce_id 和 id 进行降序排序（为后续分组时，保留重复记录中 id最高的）

3.根据 receivce_id 分组去重

4.根据id降序排序

评论(5) 最佳答案

temptation LV16 2014年10月27日

不会，帮顶

wyj LV8 2014年10月28日

是我没有理解，还是你的结果有问题，

1 -- 2 ，这个已经有两条了，还显示？

结果应该是只显示 3---4； 4--1 吧？

遇见， LV36 2014年10月28日

1SELECT d.* FROM (SELECT id, sender_id sid, receivce_id rId, content FROM message WHERE sender_id = 1 OR receivce_id = 1 GROUP BY sender_id, receivce_id) d 
2    WHERE
3    d.id NOT IN (SELECT a.id FROM(SELECT id, sender_id sid, receivce_id rId FROM message WHERE sender_id = 1 OR receivce_id = 1 GROUP BY sender_id, receivce_id) AS a
4    CROSS JOIN
5    (SELECT id, receivce_id sid, sender_id rId  FROM message WHERE sender_id = 1 OR receivce_id = 1 GROUP BY sender_id, receivce_id)
6     AS b ON a.sid = b.sid AND a.rid = b.rid AND a.sid <> 1
7);

这个行不行，写的有点麻烦。

思路是这样：

1 拿出所有与发送者ID为1交互的记录。

2 然后要去除两者互发的重复，方法是独立出来思路1的记录，将发送者和接受者位置交换，然后去两者的交集，那么交集就是两者互发的记录去除重复假如1给2发了5条 2给1发了3条在思路1 已经过滤成两条了那么不管怎么取，取出来都应该只有两条就是1 to 2 或者 2 to 1，那么接下来就需要把这两条去除就取接受ID不等于1就好了因为发送ID已经为1了那么就去出来了重复的ID。

3 在最外层取数据的时候把重复ID NOT IN一下就行了。

这是我的思路，不知道对不对。

最代码官方 LV168 2014年10月28日

先这么实现了：

1.先查到所有发私信的用户的并集

`1`	`SELECT` `receiver_id` `FROM` `message` `where` `sender_id=1` `UNION` `(SELECT` `sender_id` `FROM` `message` `where` `receiver_id=1)`

2.循环该用户列表获取到最新的那条私信数据，标示是接收者还是发送者。

伪码

1for receiver_id in receiver_ids{
2    var id="select id from message where sender_id="+receiver_id+" order by id desc";
3    if find
4        get id &发送方
5    else
6         id="select id from message where receiver_id="+receiver_id+" order by id desc";
7        get id &接收方
8}

这样有个问题是排序可能不是最新发私信的用户在最上面，优势是逻辑相比清晰，而且可以查询java层的cache加快速度。

大家有更好的解决方案请提供下吧。

318607158 2014年10月29日

帮顶

sayHelloWorld LV22 2017年3月3日

osc的解决方案

http://www.oschina.net/question/12_70252

最热评论
最新问答