我想显示一个聊天消息列表。
我有一个SELECT
语句:
SELECT id, `from`, sent, message, recd FROM chat
WHERE id IN(
SELECT MAX(id) FROM chat
WHERE `to` = ? GROUP BY `from`
)
ORDER BY id DESC
问题是,我想有条件地选择to
= maria和from
= maria的消息。
即,如果to
= maria,我想按from
分组,如果from
= maria,我想按to
分组。
如何动态改变GROUP BY
?
我想要一个UNION
(SELECT id, 'from' as direction, m_from AS fromto, sent, message, recd
FROM chat WHERE id IN (SELECT MAX(id) FROM chat WHERE to = 'Maria' GROUP BY m_from))
UNION
(SELECT id, 'to' as direction, m_to AS fromto, sent, message, recd FROM chat
WHERE id IN (SELECT MAX(id) FROM chat WHERE m_from = 'Maria' GROUP BY to))
如果选择MAX(id),则不需要使用ORDER BY id语句。
您可以在SELECT子句中添加额外的字段,就像我对'direction'所做的那样。
使用CASE结构进行条件分组:
SELECT id, `from`, sent, message, recd
FROM chat
WHERE id IN
(
SELECT MAX(id) FROM chat
WHERE 'Maria' in (`to`, `from`)
GROUP BY CASE WHEN 'Maria' = `to` THEN `from` ELSE `to` END
)
ORDER BY id DESC;
我认为所有建议的查询都很好-这意味着它们将返回预期的结果(对于从'maria'
发送或接收消息的每个不同用户都有一行)-但是效率可能不是很高。MySQL不能很好地优化WHERE column IN (complex subquery)
查询。优化器在5.6和5.7版本中有一些改进,但仍然可以更好地帮助它获得更好的计划和使用索引。
如果你想要第二个,我的建议是添加两个索引,在(to)
和(from)
上,如果你还没有(我假设表是InnoDB,主键是(id)
,所以(to)
和(from)
索引相当于(to, id)
和(from, id)
索引,这是你真正需要的查询)。
,并使用以下查询:
( SELECT id, `from`, `to`, sent, message, recd
FROM chat
WHERE `to` = ?
ORDER BY id DESC
LIMIT 1
)
UNION ALL
( SELECT id, `from`, `to`, sent, message, recd
FROM chat
WHERE `from` = ?
ORDER BY id DESC
LIMIT 1
)
ORDER BY id DESC
LIMIT 1 ;
相当于(谢谢@Thorsten Kettner):
SELECT id, `from`, `to`, sent, message, recd
FROM chat
WHERE ? IN (`to`, `from`)
ORDER BY id DESC
LIMIT 1 ;
也试试最后这个版本,如果它同样有效,就更喜欢它。没有理由使查询复杂化,除非效率不好。对于给定的索引,最后一个版本通常会同时使用它们,并使用索引合并联合算法。
在GROUP BY
中使用CASE
SELECT id, `from`, sent, message, recd
FROM chat
WHERE id IN(SELECT MAX(id)
FROM chat
WHERE `to` = ? OR `from` = ?
GROUP BY CASE
WHEN `to` = ? THEN GROUP BY `from`
ELSE `from` = ? THEN GROUP BY `to`
END)
ORDER BY id DESC