在mysql语句中使用"as"时出现未知列错误 [英] Getting unknown column error when using 'as' in mysql statement
本文介绍了在mysql语句中使用"as"时出现未知列错误的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个MySQL语句:
I have this MySQL statement:
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
键创建该列.有人知道这是怎么回事吗?
I am creating that column using the as
key. Does anyone know whats wrong here?
谢谢.
推荐答案
as
创建一个列别名(在本例中为other_id
),并且您无法加入列别名.您可以在ORDER BY
中使用别名,但不能在其他地方使用,除非别名来自子查询.
The as
creates a column alias (in this case other_id
), and you can't join on a column alias. You can use an alias in the ORDER BY
but nowhere else, unless the alias comes from a subquery.
这里最好的选择是在联接中重复IF
函数:
Your best option here would be to repeat the IF
function in the join:
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
这篇关于在mysql语句中使用"as"时出现未知列错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文