刚刚的帖子版主说写的不规范,所以只能重新写一篇了,如下:
数据库为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
现在数据库格式已经定下来是这样,所以表结构是没办法改了,所以只能求救于各位了

解决方案 »

  1.   

    版主大人,一楼只贴了数据,没有写需求,需求是求和王五最后聊天的2个用户的用户信息,以及聊天的最后一条信息的内容和发表时间:
    希望的结果如下:id     username content         createDate
    1 赵六 消息....15 2013-07-04 15:40:37
    2 丁七 消息....20 2013-07-04 15:40:37
      

  2.   

    mysql> select * from user_info;
    +----+----------+
    | 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>
      

  3.   

    不知道你的记录是否有代表性
    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