SQL替代FROM中的子查询

debugcn 发表于 Dev

点头

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

我可以使用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小提琴：http ：//www.sqlfiddle.com/#！2/845275/2

没有子查询，有没有办法做到这一点？
（编写仅在“ IN”中支持子查询的DQL）

戈登·利诺夫（Gordon Linoff）

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

MySQL扩展了GROUP BY的使用，以便选择列表可以引用未在GROUP BY子句中命名的非聚合列。这意味着前面的查询在MySQL中是合法的。您可以使用此功能来避免不必要的列排序和分组，从而获得更好的性能。但是，这主要在每个组的每个未聚合列中未在GROUP BY中命名的所有值都相同时才有用。服务器可以从每个组中自由选择任何值，因此，除非它们相同，否则选择的值是不确定的。此外，通过添加ORDER BY子句不能影响从每个组中选择值。选择值之后，将对结果集进行排序，并且ORDER BY不会影响服务器在每个组中选择哪个值。

您想要的查询更像是这样（假设您有的自动递增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)将有助于提高性能。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。