SQL语句查询最近联系人聊天记录(即最近一条消息)，获取到的类似微信或者qq的消息列表
网上大部分是错误的，下面看下我的表:
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `tbl05`
-- ----------------------------
DROP TABLE IF EXISTS `tbl05`;
CREATE TABLE `tbl05` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`from` int(11) NOT NULL,
`to` int(11) NOT NULL,
`msg` varchar(255) NOT NULL,
`tline` int(11) NOT NULL DEFAULT '0',
`aaaa` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tbl05
-- ----------------------------
INSERT INTO `tbl05` VALUES ('1', '1', '2', '555555', '10', '');
INSERT INTO `tbl05` VALUES ('2', '1', '3', 'hhhhhh', '10', '');
INSERT INTO `tbl05` VALUES ('3', '3', '1', 'ooooooooo', '10', '');
INSERT INTO `tbl05` VALUES ('4', '2', '3', 'hhhhhh', '10', '');
INSERT INTO `tbl05` VALUES ('5', '3', '2', '888888888888888', '11', '');
INSERT INTO `tbl05` VALUES ('6', '2', '1', '我就是 神', '9', '');
INSERT INTO `tbl05` VALUES ('7', '2', '1', 'hhhhhh', '12', '');
INSERT INTO `tbl05` VALUES ('8', '4', '5', '你好', '0', '');
INSERT INTO `tbl05` VALUES ('9', '5', '4', '你也好', '1', '');
INSERT INTO `tbl05` VALUES ('10', '4', '5', '你是哪里的', '2', '');
INSERT INTO `tbl05` VALUES ('11', '5', '4', '我是北京的', '3', '');建好表 后，看下查询语句
SELECT T1.id, T1.`from`, T1.`to`, T1.msg, T1.tline FROM
(
SELECT id, `from`, `to`, tline, msg,
CASE
WHEN (`from` <= `to`)
THEN CONCAT(`from`, '-', `to`)
ELSE CONCAT(`to`, '-', `from`)
END AS ft FROM tbl05) AS T1
INNER JOIN
(
SELECT MAX(tline) AS tline,
CASE
WHEN (`from` <= `to`)
THEN CONCAT(`from`, '-', `to`)
ELSE CONCAT(`to`, '-', `from`)
END AS ft
FROM tbl05
GROUP BY ft
) AS T2
ON T1.tline = T2.tline AND T1.ft = T2.ft
以上 为查询到所有的 消息列表 。
如果查询 1号用户的 消息列表 在末尾增加 where T1.`from`= 1 or T1.`to`=1
查询 到 1号用户 的消息列表，并增加 对方的 信息
left join test_user as U on
case WHEN T1.fasongid=1 THEN T1.jieshouid = U.userid
ELSE T1.fasongid = U.userid END
完整代码：
SELECT T1.id, T1.`fasongid`, T1.`jieshouid`, T1.neirong, T1.shijian,U.nicheng from
(
SELECT id, `fasongid`, `jieshouid`, shijian, neirong,
CASE
WHEN (`fasongid` <= `jieshouid`)
THEN CONCAT(`fasongid`, '-', `jieshouid`)
ELSE CONCAT(`jieshouid`, '-', `fasongid`)
END AS ft from test_liaotian) AS T1
INNER JOIN
(
SELECT MAX(shijian) AS shijian,
CASE
WHEN (`fasongid` <= `jieshouid`)
THEN CONCAT(`fasongid`, '-', `jieshouid`)
ELSE CONCAT(`jieshouid`, '-', `fasongid`)
END AS ft
from test_liaotian
GROUP BY ft
) AS T2
ON T1.shijian = T2.shijian AND T1.ft = T2.ft
left join test_user as U on
case WHEN T1.fasongid=1 THEN T1.jieshouid = U.userid
ELSE T1.fasongid = U.userid END
where T1.`fasongid`= 1 or T1.`jieshouid`=1
注： 字段名改了，使用了 test_user表，完整的表设计可以找站长要
