我从此查询中获得未知列错误。如何使用此类查询?

SELECT
    (CASE
        WHEN(c.from_user=144)
        THEN c.to_user
        ELSE c.from_user
    END) as chatted_to
FROM `chat_message` `c`
LEFT JOIN `user`
ON chatted_to = user.id
WHERE (`to_user`=144) OR (`from_user`=144)
GROUP BY `chatted_to`


它说:


  “ on子句”中的未知列“ chatted_to”。

最佳答案

您可以在CASE子句中再次重复ON条件,如下所示:

SELECT
    (CASE
        WHEN(c.from_user=144) THEN c.to_user
        ELSE c.from_user
    END) as chatted_to
FROM `chat_message` `c`
LEFT JOIN `user`
ON
    (CASE
        WHEN(c.from_user=144) THEN c.to_user
        ELSE c.from_user
    END) = user.id
WHERE (`to_user`=144) OR (`from_user`=144)
GROUP BY `chatted_to`

关于mysql - 连接到另一个表时,如何使用稍后在MYSQL中选择作为查询添加的列,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/50306829/

10-11 02:22