2个表
1.用户
字段: id, name
用户id,用户名
2.聊天表
字段: id, fromUserId, toUserId, content, date
id, 发送方id,接收方id, 发送内容, 发送日期现在要查出 当前用户发过信息,或者给当前用户发过信息的,
最后5个用户信息,以及最后发表的那条信息的内容,发送时间谢谢了
1.用户
字段: id, name
用户id,用户名
2.聊天表
字段: id, fromUserId, toUserId, content, date
id, 发送方id,接收方id, 发送内容, 发送日期现在要查出 当前用户发过信息,或者给当前用户发过信息的,
最后5个用户信息,以及最后发表的那条信息的内容,发送时间谢谢了
CREATE TABLE `chat_message` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`fromUserId` bigint(20) NOT NULL,
`toUserId` bigint(20) NOT NULL,
`content` varchar(255) NOT NULL,
`createDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8;INSERT INTO `chat_message` VALUES ('1', '1', '2', '张三到李四1', '2013-07-04 15:32:40');
INSERT INTO `chat_message` VALUES ('2', '1', '3', '张三到王五1', '2013-07-04 15:33:04');
INSERT INTO `chat_message` VALUES ('3', '1', '4', '张三到赵六1', '2013-07-04 15:34:39');
INSERT INTO `chat_message` VALUES ('4', '2', '1', '消息....112', '2013-07-04 15:38:56');
INSERT INTO `chat_message` VALUES ('5', '2', '3', '消息....113', '2013-07-04 15:38:56');
INSERT INTO `chat_message` VALUES ('6', '2', '4', '消息....114', '2013-07-04 15:38:56');
INSERT INTO `chat_message` VALUES ('7', '2', '5', '消息....115', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('8', '3', '1', '消息....116', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('9', '3', '2', '消息....117', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('10', '3', '4', '消息....118', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('11', '3', '5', '消息....119', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('12', '4', '1', '消息....120', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('13', '4', '2', '消息....121', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('14', '4', '3', '消息....122', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('15', '4', '5', '消息....123', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('16', '5', '1', '消息....124', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('17', '5', '2', '消息....125', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('18', '5', '4', '消息....126', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('19', '5', '3', '消息....127', '2013-07-04 15:38:57');
INSERT INTO `chat_message` VALUES ('20', '1', '2', '消息....1', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('21', '1', '3', '消息....2', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('22', '1', '4', '消息....3', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('23', '1', '5', '消息....4', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('24', '2', '1', '消息....5', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('25', '2', '3', '消息....6', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('26', '2', '4', '消息....7', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('27', '2', '5', '消息....8', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('28', '3', '1', '消息....9', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('29', '3', '2', '消息....10', '2013-07-04 15:40:36');
INSERT INTO `chat_message` VALUES ('30', '3', '4', '消息....11', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('31', '3', '5', '消息....12', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('32', '4', '1', '消息....13', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('33', '4', '2', '消息....14', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('34', '4', '3', '消息....15', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('35', '4', '5', '消息....16', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('36', '5', '1', '消息....17', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('37', '5', '2', '消息....18', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('38', '5', '4', '消息....19', '2013-07-04 15:40:37');
INSERT INTO `chat_message` VALUES ('39', '5', '3', '消息....20', '2013-07-04 15:40:37');
需要查询 和王五聊天过的(王五主动发送,或者被动接收的都算)最后2个用户的信息,以及最后那2条信息的内容和发表时间
-- Table structure for `user_info`
-- ----------------------------
DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of user_info
-- ----------------------------
INSERT INTO `user_info` VALUES ('1', '张三');
INSERT INTO `user_info` VALUES ('2', '李四');
INSERT INTO `user_info` VALUES ('3', '王五');
INSERT INTO `user_info` VALUES ('4', '赵六');
INSERT INTO `user_info` VALUES ('5', '丁七');
1 张三 消息....114 2013-07-04 15:38:56
2 李四 消息....114 2013-07-04 15:38:56
希望是这样的结果
1 张三 消息....114 2013-07-04 15:38:56
2 李四 消息....114 2013-07-04 15:38:56
希望是这样的结果这个结果是根本什么逻辑出来的?详细描述一下。
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
+----+----------+
| id | username |
+----+----------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
| 4 | 赵六 |
| 5 | 丁七 |
+----+----------+
5 rows in set (0.00 sec)mysql> select * from chat_message;
+----+------------+----------+-------------+---------------------+
| id | fromUserId | toUserId | content | createDate |
+----+------------+----------+-------------+---------------------+
| 1 | 1 | 2 | 张三到李四1 | 2013-07-04 15:32:40 |
| 2 | 1 | 3 | 张三到王五1 | 2013-07-04 15:33:04 |
| 3 | 1 | 4 | 张三到赵六1 | 2013-07-04 15:34:39 |
| 4 | 2 | 1 | 消息....112 | 2013-07-04 15:38:56 |
| 5 | 2 | 3 | 消息....113 | 2013-07-04 15:38:56 |
| 6 | 2 | 4 | 消息....114 | 2013-07-04 15:38:56 |
| 7 | 2 | 5 | 消息....115 | 2013-07-04 15:38:57 |
| 8 | 3 | 1 | 消息....116 | 2013-07-04 15:38:57 |
| 9 | 3 | 2 | 消息....117 | 2013-07-04 15:38:57 |
| 10 | 3 | 4 | 消息....118 | 2013-07-04 15:38:57 |
| 11 | 3 | 5 | 消息....119 | 2013-07-04 15:38:57 |
| 12 | 4 | 1 | 消息....120 | 2013-07-04 15:38:57 |
| 13 | 4 | 2 | 消息....121 | 2013-07-04 15:38:57 |
| 14 | 4 | 3 | 消息....122 | 2013-07-04 15:38:57 |
| 15 | 4 | 5 | 消息....123 | 2013-07-04 15:38:57 |
| 16 | 5 | 1 | 消息....124 | 2013-07-04 15:38:57 |
| 17 | 5 | 2 | 消息....125 | 2013-07-04 15:38:57 |
| 18 | 5 | 4 | 消息....126 | 2013-07-04 15:38:57 |
| 19 | 5 | 3 | 消息....127 | 2013-07-04 15:38:57 |
| 20 | 1 | 2 | 消息....1 | 2013-07-04 15:40:36 |
| 21 | 1 | 3 | 消息....2 | 2013-07-04 15:40:36 |
| 22 | 1 | 4 | 消息....3 | 2013-07-04 15:40:36 |
| 23 | 1 | 5 | 消息....4 | 2013-07-04 15:40:36 |
| 24 | 2 | 1 | 消息....5 | 2013-07-04 15:40:36 |
| 25 | 2 | 3 | 消息....6 | 2013-07-04 15:40:36 |
| 26 | 2 | 4 | 消息....7 | 2013-07-04 15:40:36 |
| 27 | 2 | 5 | 消息....8 | 2013-07-04 15:40:36 |
| 28 | 3 | 1 | 消息....9 | 2013-07-04 15:40:36 |
| 29 | 3 | 2 | 消息....10 | 2013-07-04 15:40:36 |
| 30 | 3 | 4 | 消息....11 | 2013-07-04 15:40:37 |
| 31 | 3 | 5 | 消息....12 | 2013-07-04 15:40:37 |
| 32 | 4 | 1 | 消息....13 | 2013-07-04 15:40:37 |
| 33 | 4 | 2 | 消息....14 | 2013-07-04 15:40:37 |
| 34 | 4 | 3 | 消息....15 | 2013-07-04 15:40:37 |
| 35 | 4 | 5 | 消息....16 | 2013-07-04 15:40:37 |
| 36 | 5 | 1 | 消息....17 | 2013-07-04 15:40:37 |
| 37 | 5 | 2 | 消息....18 | 2013-07-04 15:40:37 |
| 38 | 5 | 4 | 消息....19 | 2013-07-04 15:40:37 |
| 39 | 5 | 3 | 消息....20 | 2013-07-04 15:40:37 |
+----+------------+----------+-------------+---------------------+
39 rows in set (0.00 sec)mysql> select username,content,createDate
-> from (
-> select m.id,m.toUserId as uid,m.content,m.createDate
-> from chat_message m,user_info u
-> where m.fromUserId=u.id
-> and u.username='王五'
-> union all
-> select m.id,m.fromUserId,m.content ,m.createDate
-> from chat_message m, user_info u
-> where m.toUserId=u.id
-> and u.username='王五'
-> order by createDate desc
-> ) t, user_info v
-> where t.uid=v.id
-> group by uid
-> order by createDate desc
-> limit 2;
+----------+------------+---------------------+
| username | content | createDate |
+----------+------------+---------------------+
| 赵六 | 消息....15 | 2013-07-04 15:40:37 |
| 丁七 | 消息....20 | 2013-07-04 15:40:37 |
+----------+------------+---------------------+
2 rows in set (0.02 sec)mysql>