刚刚的帖子版主说写的不规范,所以只能重新写一篇了,如下:
数据库为mysql以下为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', '丁七');
DROP TABLE IF EXISTS `chat_message`;
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');
需求是这样的:
我们现在做一个聊天系统,用户可以查看之前和哪些用户聊过天,类似于QQ上的聊天历史记录列表,
需要有 用户名称, 最后聊天内容,以及最后聊天内容的时间, 格式类似如下:
id username content createDate
1 张三 消息....114 2013-07-04 15:38:56
2 李四 消息....114 2013-07-04 15:38:56
现在数据库格式已经定下来是这样,所以表结构是没办法改了,所以只能求救于各位了
数据库为mysql以下为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', '丁七');
DROP TABLE IF EXISTS `chat_message`;
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');
需求是这样的:
我们现在做一个聊天系统,用户可以查看之前和哪些用户聊过天,类似于QQ上的聊天历史记录列表,
需要有 用户名称, 最后聊天内容,以及最后聊天内容的时间, 格式类似如下:
id username content createDate
1 张三 消息....114 2013-07-04 15:38:56
2 李四 消息....114 2013-07-04 15:38:56
现在数据库格式已经定下来是这样,所以表结构是没办法改了,所以只能求救于各位了
希望的结果如下:id username content createDate
1 赵六 消息....15 2013-07-04 15:40:37
2 丁七 消息....20 2013-07-04 15:40:37
+----+----------+
| 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>
SELECT * FROM chat_message a
INNER JOIN
`user_info` b
ON IF(a.`toUserId`<>3,a.`toUserId`,a.`fromUserId`)=b.`id`
WHERE a.`fromUserId`=3 OR a.`toUserId`=3
ORDER BY a.`createDate` DESC LIMIT 2