如何对MessageTable短信表按联系人分组后按日期时间交叉排序

问题描述 #

我现有一张名为MessageTable的短信表，存储了用户Ali的手机短信收发记录，包含Ali接收他人短信和向他人发送的回复短信，表结构和数据如下：

需要对该表进行排序，满足以下要求：首先将Ali与每位联系人的所有往来短信单独归为一组排列，其次每组内的短信按发送日期和时间升序排列，预期排序效果如下：

请问如何编写对应的SQL代码实现上述排序需求？

解决方案 #

核心思路是先提取每条短信对应的联系人（即sender和receiver中不是Ali的那一方），先按联系人排序实现同组聚集，再按发送日期和时间升序排列组内内容，对应SQL代码如下：

SELECT *
FROM MessageTable
ORDER BY
  -- 提取往来联系人，按联系人分组排序
  CASE WHEN sender = 'Ali' THEN receiver ELSE sender END,
  -- 组内按发送时间升序排列
  sendDate ASC,
  sendTime ASC;

说明 #

1. CASE判断逻辑会自动识别每条记录对应的联系人：如果Ali是发送方，取接收方为联系人；如果Ali是接收方，取发送方为联系人，保证同一位联系人的所有往来短信会被归为同一组
2. 如果你的数据库中sendDate为字符串类型无法直接按日期逻辑排序，可以先通过日期转换函数处理，例如MySQL可以用STR_TO_DATE(sendDate, '%Y/%m/%d')替换排序条件中的sendDate，避免字符串排序出错。

内容的提问来源于stack exchange，提问作者honey bee