当列中的值互换时，MySQL用2列分组

SELECT m1.*FROM message AS m1 JOIN (SELECT Concat(Least(m.from_number, m.to_number), '|', Greatest(m.from_number, m.to_number)) AS thread, Max(m.generated_time) AS max_generated_time FROM message AS m GROUP BY thread HAVING Sum(m.direction = 'INCOMING' AND m.type = 'REVIEW')) AS dt ON dt.thread = Concat(Least(m1.from_number, m1.to_number), '|', Greatest(m1.from_number, m1.to_number)) AND dt.max_generated_time = m1.generated_time; 结果 | id | to_number | from_number | message | direction | type | generated_time || --- | ------------ | ------------ | --------------- | --------- | ------ | ------------------- || 3 | [phone] | [phone] | How are you ? | OUTGOING | | 2019-07-13 21:15:00 || 5 | [phone] | [phone] | Have a nice day | INCOMING | REVIEW | 2019-07-12 12:17:00 | 边注: 上述方法(和您当前的方案设计)无法使用索引，因此不会表现出色.我宁愿通过创建两个其他主表来重新设计架构.一个主表将存储电话号码:phone_id和number 另一个主表将存储"Thread"，其中将包含phone_id值和thread_id.然后，您可以在message表中使用此thread_id，而不是存储电话号码.Above approach (and your current schema design) is not able to use indexes, and hence it will not be performant.I would rather redesign the schema by creating two additional Master tables. One Master table would be storing the phone numbers: phone_id, and numberAnother Master table would be storing the "Thread", which will contain the phone_id values and thread_id. You can then use this thread_id in your message table, instead of storing the phone numbers. 这篇关于当列中的值互换时，MySQL用2列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！