MySQL跨表时间戳比较并按最新记录排序异常排查

我需要从表中获取数据，期望根据swipes和messages两个表的时间戳排序，最新记录排在最前面，但当前仅messages表的时间戳生效，swipes表的更新记录无法优先显示。

使用的查询语句如下：

SELECT usr.id, usr.gender, usr.last_activity, CONCAT(usr.first_name, ' ', usr.last_name) as fullName FROM
(SELECT swp_to uid, created_at cat FROM swipes WHERE swp_from = :user AND first_swp IN ('like','superlike') AND second_swp IN ('like','superlike')
  UNION
  SELECT swp_from uid, created_at cat FROM swipes WHERE swp_to = :user AND first_swp IN ('like','superlike') AND second_swp IN ('like','superlike')
) u
LEFT JOIN messages m ON u.uid IN (m.msg_from, m.msg_to)
INNER JOIN users usr ON u.uid = usr.id
GROUP BY u.uid ORDER BY GREATEST(MAX(m.created_at), MAX(u.cat)) DESC LIMIT $start, $limit

我猜测GREATEST()函数未按预期工作，它仅依据MAX(m.created_at)排序，忽略了MAX(u.cat)，请问哪里出错了？

错误原因 #

因为使用了LEFT JOIN messages，当某个用户没有对应的消息记录时，MAX(m.created_at)会返回NULL。而GREATEST()函数只要有一个参数为NULL，整体结果就会是NULL，在排序时NULL会被默认排在末尾，导致swipes表的时间戳无法生效。

修正方案 #

用COALESCE()函数把MAX(m.created_at)的NULL值替换成一个极小的时间值（比如'1970-01-01 00:00:00'），这样当没有消息记录时，GREATEST()会正确取MAX(u.cat)的值进行排序。

修正后的查询语句：

SELECT usr.id, usr.gender, usr.last_activity, CONCAT(usr.first_name, ' ', usr.last_name) as fullName FROM
(SELECT swp_to uid, created_at cat FROM swipes WHERE swp_from = :user AND first_swp IN ('like','superlike') AND second_swp IN ('like','superlike')
  UNION
  SELECT swp_from uid, created_at cat FROM swipes WHERE swp_to = :user AND first_swp IN ('like','superlike') AND second_swp IN ('like','superlike')
) u
LEFT JOIN messages m ON u.uid IN (m.msg_from, m.msg_to)
INNER JOIN users usr ON u.uid = usr.id
GROUP BY u.uid 
ORDER BY GREATEST(COALESCE(MAX(m.created_at), '1970-01-01 00:00:00'), MAX(u.cat)) DESC 
LIMIT $start, $limit

额外说明 #

如果你的数据库中created_at是时间戳类型（数字形式），可以把'1970-01-01 00:00:00'替换成0，效果一致。

内容的提问来源于stack exchange，提问作者Super Coder