我想知道数据库中的用户状态是否发生了更改
我这里有这个代码
SELECT `users`.`status`,`users`.`id_user`,
(SELECT CASE
WHEN `timestamp` >= "2013-11-20 15:30:36"
THEN 2
WHEN `timestamp` >= "2013-11-20 15:30:51"
THEN 1
ELSE 0
END AS `status1`
FROM `users_actions` as `users_actions`
ORDER BY `timestamp` DESC
LIMIT 1) as `status1`
FROM `users` as `users` WHERE id_user IN (5,22)
AND `users`.`status` != `users`.`status1`
它给了我这个错误Unknown column 'users.status1' in 'where clause'
我的问题是如何将临时元素与列进行比较
子查询的别名为status1,case语句的别名为status1,因此要引用子查询之外的字段,它将是status1.status1而不是user.status1
更新(2013年11月21日)
SELECT users.status,
users.id_user,
status1.status1
FROM users as users
,(SELECT CASE
WHEN users_actions.timestamp >= "2013-11-20 15:30:36"
THEN 2
WHEN users_actions.timestamp >= "2013-11-20 15:30:51"
THEN 1
ELSE 0
END AS status1
FROM users_actions as users_actions
ORDER BY users_actions.timestamp DESC
LIMIT 1) as status1
WHERE users.id_user IN (5,22)
AND users.status != users.status1
我使用您创建的别名更新了您的查询,以确保一切正常。如果你不打算使用别名,为什么要创建别名?我还重新安排了你的FROM语句,看看这是否解决了问题。