我有一个查询:
SELECT b.user_id, b.active FROM users b
WHERE b.followers_count != (SELECT COUNT(*)
FROM (SELECT u.user_id
FROM user_follow uf,users u,user_follow_request ufr
WHERE
uf.following_id = b.user_id AND
uf.following_id = ufr.friend_id AND
ufr.status = 'approved' AND
ufr.user_id = u.user_id AND u.user_id != b.user_id AND u.active != 0
GROUP BY u.user_id) a)
AND b.active = -1 limit 5;
它必须从
user_id
中选择与sql计算的列中具有不同users
的所有followers_count
。但是问题是我收到错误消息
Error Code: 1054. Unknown column 'b.user_id' in 'on clause'
我做错了什么?非常感谢您的帮助。
最佳答案
您是说uf.user_id = b.user_id
而不是uf.following_id = b.user_id
吗?无论如何,user_follow
表用于什么?
同意Strawberry,您最好使用显式联接。它们更容易阅读和理解。这是您可能已经提到的内容(请注意,根本不使用user_follow
表):
SELECT u.user_id, u.followers_count, IF(ISNULL(uafc.actual_fc),0,uafc.actual_fc) AS afc
FROM users AS u
LEFT JOIN (
SELECT u.user_id AS user_id, COUNT(*) AS actual_fc
FROM users AS u
JOIN user_follow_request AS ufr
ON ufr.friend_id = u.user_id
JOIN users AS fu
ON fu.user_id = ufr.user_id
WHERE ufr.user_id != u.user_id
AND ufr.status = 'approved'
AND fu.active != 0
GROUP BY u.user_id, u.followers_count
) AS uafc
ON uafc.user_id = u.user_id
WHERE u.followers_count != IF(ISNULL(uafc.actual_fc),0,uafc.actual_fc)
;
可能有一些更好的NULL解决方法。
在SQLFiddle上查看:http://sqlfiddle.com/#!2/71f6c/3