android - 将MySQL查询转换为SQLite

我得到以下MySQL查询，我需要将其转换为SQLite查询（在我的android应用程序中使用）

select *
from msgsList
where (least(msg_from, msg_to), greatest(msg_from, msg_to), msg_time)
in
(
    select
       least(msg_from, msg_to) as x, greatest(msg_from, msg_to) as y,
       max(msg_time) as msg_time
    from msgsList
    group by x, y
);

最佳答案

在SQLite中，LEAST（）/ GREATEST（）仅称为MIN（）/ MAX（）。

SQLite不支持带元组的IN，但是您可以将表连接到子查询：

SELECT msgsList.*
FROM msgsList
JOIN (SELECT MIN(msg_from, msg_to) AS x,
             MAX(msg_from, msg_to) AS y,
             MAX(msg_time) AS msg_time
     FROM msgsList
     GROUP BY x, y) AS m
ON m.x        = MIN(msgsList.msg_from, msgsList.msg_to) AND
   m.y        = MAX(msgsList.msg_from, msgsList.msg_to) AND
   m.msg_time = msgsList.msg_time;

如果您具有SQLite 3.7.11或更高版本（即not available in all Android versions），则可以简单地使用聚合MAX（）选择要返回的行：

SELECT *,
       MAX(msg_time)
FROM msgsList
GROUP BY MIN(msg_from, msg_to),
         MAX(msg_from, msg_to);