最代码官方的gravatar头像
最代码官方 2014-10-27 17:55:18

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

私信表结构如下:

建表语句和数据

CREATE TABLE `message` (
  `id` int(5) NOT NULL,
  `sender_id` int(5) DEFAULT NULL,
  `receivce_id` int(5) DEFAULT NULL,
  `content` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;



/*Data for the table `message` */



insert  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

所有回答列表(7)
woniu的gravatar头像
woniu 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的gravatar头像
temptation LV2 2014年10月27日

不会,帮顶

wyj的gravatar头像
wyj 2014年10月28日

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

1 -- 2 ,这个已经有两条了,还显示?  

结果应该是只显示 3---4; 4--1 吧?

遇见,的gravatar头像
遇见, LV2 2014年10月28日
SELECT 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 
	WHERE
	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
	CROSS JOIN
	(SELECT id, receivce_id sid, sender_id rId  FROM message WHERE sender_id = 1 OR receivce_id = 1 GROUP BY sender_id, receivce_id)
	 AS b ON a.sid = b.sid AND a.rid = b.rid AND a.sid <> 1
);

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

思路是这样:

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一下就行了。

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

最代码官方的gravatar头像
最代码官方 LV4 2014年10月28日

先这么实现了:

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

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

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

伪码

for receiver_id in receiver_ids{
    var id="select id from message where sender_id="+receiver_id+" order by id desc";
    if find
        get id &发送方
    else
         id="select id from message where receiver_id="+receiver_id+" order by id desc";
        get id &接收方
}

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

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

318607158的gravatar头像
318607158 2014年10月29日

帮顶

sayHelloWorld的gravatar头像
sayHelloWorld 3月3日

osc的解决方案

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

顶部 客服 微信二维码 底部
>扫描二维码关注最代码为好友扫描二维码关注最代码为好友