mysql - SQL替代FROM中的子查询

Question

我有一个包含用户到用户消息的表。对话包含两个用户之间的所有消息。我正在尝试获取所有不同对话的列表，并仅显示列表中发送的最后一条消息。

我可以使用 FROM 中的 SQL 子查询来做到这一点。

CREATE TABLE `messages` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `from_user_id` bigint(20) DEFAULT NULL,
 `to_user_id` bigint(20) DEFAULT NULL,
 `type` smallint(6) NOT NULL,
 `is_read` tinyint(1) NOT NULL,
 `is_deleted` tinyint(1) NOT NULL,
 `text` longtext COLLATE utf8_unicode_ci NOT NULL,
 `heading` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `created_at_utc` datetime DEFAULT NULL,
 `read_at_utc` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
);


SELECT * FROM
 (SELECT * FROM `messages` WHERE TYPE = 1 AND
 (from_user_id = 22 OR to_user_id = 22)
 ORDER BY created_at_utc DESC
 ) tb
GROUP BY from_user_id, to_user_id;

SQL fiddle :
http://www.sqlfiddle.com/#!2/845275/2

有没有不用子查询的方法？
(写一个只支持'IN'子查询的DQL)

Answer 1

您似乎正在尝试从类型 = 1 的用户 22 获取消息的最后内容。您的方法显然不能保证有效，因为额外的列(不在 group by) 可以来自任意行。如 [文档][1] 中所述:

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.

您想要的查询更符合以下内容(假设您有一个自动递增的 id 列用于 messages):

select m.*
from (select m.from_user_id, m.to_user_id, max(m.id) as max_id
      from message m
      where m.type = 1 and (m.from_user_id = 22 or m.to_user_id = 22)
     ) lm join
     messages m 
     on lm.max_id = m.id;

或者这个:

select m.*
from message m
where m.type = 1 and (m.from_user_id = 22 or m.to_user_id = 22) and
      not exists (select 1
                  from messages m2
                  where m2.type = m.type and m2.from_user_id = m.from_user_id and
                        m2.to_user_id = m.to_user_id and
                        m2.created_at_utc > m.created_at_utc
                 );

对于后一个查询，messages(type, from_user_id, to_user_id, created_at_utc) 上的索引将有助于提高性能。