Mysql Group By后的查询问题

Question

现在有一个聊天记录表，需要按时间倒序获取最近联系人列表，以及与每个联系人的最后一次聊天记录和对方发给自己的消息的未读数目。现在卡在Group By后怎样获取最近聊天内容和未读消息数目上了。表结构如下+------------+------------------+------+-----+-------------------+-----------------------------+
| Field      | Type             | Null | Key | Default           | Extra                       |
+------------+------------------+------+-----+-------------------+-----------------------------+
| cid        | int(10) unsigned | NO   | PRI | NULL              | auto_increment              |
| from       | int(10) unsigned | NO   | MUL | NULL              |                             |
| to         | int(10) unsigned | NO   | MUL | NULL              |                             |
| content    | text             | NO   |     | NULL              |                             |
| is_readed  | tinyint(1)       | NO   |     | 0                 |                             |
| created_at | timestamp        | NO   |     | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP |
+------------+------------------+------+-----+-------------------+-----------------------------+
from是发送者，to是接收者，content是聊天内容，is_readed表示消息是否已读。现在假设有数据如下：+-----+--------+--------+----------+-----------+---------------------+
| cid | from   | to     | content  | is_readed | created_at          |
+-----+--------+--------+----------+-----------+---------------------+
|   1 | 100000 | 100001 | first    |         0 | 2017-05-24 07:14:29 |
|   2 | 100001 | 100000 | second   |         0 | 2017-05-24 07:14:55 |
|   3 | 100002 | 100001 | third    |         0 | 2017-05-24 07:15:30 |
|   4 | 100001 | 100000 | ffffffff |         1 | 2017-05-24 08:31:42 |
|   5 | 100001 | 100002 | eeeeeeee |         0 | 2017-05-24 07:34:47 |
+-----+--------+--------+----------+-----------+---------------------+-- ----------------------------
-- Table structure for chats
-- ----------------------------
DROP TABLE IF EXISTS `chats`;
CREATE TABLE `chats` (
  `cid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `from` int(10) unsigned NOT NULL,
  `to` int(10) unsigned NOT NULL,
  `content` text COLLATE utf8_unicode_ci NOT NULL,
  `is_readed` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cid`),
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;-- ----------------------------
-- Records of chats
-- ----------------------------
INSERT INTO `chats` VALUES ('1', '100000', '100001', 'first', '0', '2017-05-24 07:14:29');
INSERT INTO `chats` VALUES ('2', '100001', '100000', 'second', '0', '2017-05-24 07:14:55');
INSERT INTO `chats` VALUES ('3', '100002', '100001', 'third', '0', '2017-05-24 07:15:30');
INSERT INTO `chats` VALUES ('4', '100001', '100000', 'ffffffff', '1', '2017-05-24 08:31:42');
INSERT INTO `chats` VALUES ('5', '100001', '100002', 'eeeeeeee', '0', '2017-05-24 07:34:47');如果我的编号是100001
那么我想获取的数据应该是：+--------+----------------+------------+---------------------+
| uid    | recent_content | unread_num | time                |
+--------+----------------+------------+---------------------+
| 100000 | ffffffff       |          1 | 2017-05-24 08:31:42 |
| 100002 | eeeeeeee       |          1 | 2017-05-24 07:34:47 |
+--------+----------------+------------+---------------------+
Mysql里面没有FIRST聚合函数，也不知道怎样对COUNT加条件，求大神给写个SQL学习学习。

Answer 1

count 加条件可以用 count(case when 条件 then 1 end) 表示 满中条件统计，不逃跑条件不统计（利用 null 不会统计的特性）

Answer 2

first 确实就不好弄，如果你每次只查一个编号，可以用 order by xx limit 1 来处理
如果你要查多个编号，那么通常的方法是  select 编号,min(日期) from tb group by 编号 取得每个编号的最早日期，然后再 join 原始数据表得到最早日期对应的其他列数据（编号 + 日期需要能够唯一确定一条记录)

Answer 3

那我这样写可行吗？SELECT
a.uid,
a.unread_num,
c.content AS recent_content,
a.time
FROM
(
SELECT
uid,
sum(unread_num) AS unread_num,
max(_cid) AS _cid,
max(time) AS time
FROM
(
SELECT
`to` AS uid,
0 AS unread_num,
max(cid) AS _cid,
max(created_at) AS time
FROM
chats
WHERE
`from` = 100001
GROUP BY
`to`
UNION
SELECT
`from` AS uid,
count(
CASE
WHEN is_readed = FALSE THEN
1
END
) AS unread_num,
max(cid) AS _cid,
max(created_at) AS time
FROM
chats
WHERE
`to` = 100001
GROUP BY
`from`
) AS b
GROUP BY
uid
ORDER BY
time DESC
) AS a
LEFT JOIN chats AS c ON c.cid = a._cid我是觉得既然cid是自增的，那么cid越大，聊天记录应该就越新，所以找最大的自增编号来left join查询最新聊天记录。不知实际使用中会不会出什么问题？

Answer 4

参考下贴中的多种方法http://blog.csdn.net/acmain_chm/article/details/4126306
[征集]分组取最大N条记录方法征集，及散分....

Answer 5

通常自增列最大也就是最新是可行的，除非你有手工调整时间，或者程序上有延迟的 insert 之类的特殊情况

Answer 6

根据您的博客内容，我用where not exists实现了查询最新的聊天记录，但是却找不到方法查询未读消息数目，您看看是否能给个解决方法：select 
IF(a.`from` = 100001,`to`,`from`) uid,
a.content recent_content,
a.created_at `time`
from chats a
where not exists
(
select 
* 
from chats b 
where ((a.`from` = b.`from` and a.`to` = b.`to`) or(a.`from` = b.`to` and a.`to` = b.`from`)) 
and a.created_at < b.created_at
)
and (a.`from` = 100001 or a.`to` = 100001)