mysql - 在 mysql 语句中使用 'as' 时出现未知列错误

Question

我有这个 MySQL 语句:

SELECT a.id, a.`from member_id`, a.`to member_id`, IF(a.`from member_id`=1, a.`to member_id`, a.`from member_id`) as other_id, a.text, MAX(a.`date sent`) as `date sent`
FROM message a
JOIN members m on other_id=m.id
WHERE (a.`from member_id`=1 OR a.`to member_id`=1) AND a.active=1
GROUP BY other_id
ORDER BY other_id DESC, `date sent` DESC

但是我得到了错误:

#1054 - Unknown column 'other_id' in 'on clause' 

我正在使用 as 键创建该列。有谁知道这里出了什么问题？

谢谢。

Answer 1

as 创建了一个列别名(在本例中为 other_id)，您不能在列别名上加入。您可以在 ORDER BY 中使用别名，但不能在其他地方使用，除非别名来自子查询。

这里最好的选择是在连接中重复 IF 函数:

SELECT
  a.id,
  a.from member_id,
  a.to member_id,
  IF(a.from member_id=1, a.to member_id, a.from member_id) as other_id,
  a.text,
  MAX(a.date sent) as date sent
FROM message a
JOIN members m on IF(a.from member_id=1, a.to member_id, a.from member_id) = m.id
WHERE (a.from member_id=1 OR a.to member_id=1) AND a.active=1
GROUP BY other_id
ORDER BY other_id DESC, date sent DESC