问题描述
以下 MySQL 查询:
The following MySQL query:
select `userID` as uID,
(select `siteID` from `users` where `userID` = uID) as `sID`,
from `actions`
where `sID` in (select `siteID` from `sites` where `foo` = "bar")
order by `timestamp` desc limit 100
…返回错误:
Unknown column 'sID' in 'IN/ALL/ANY subquery'
我不明白我在这里做错了什么.sID 东西不应该是一列，而是我通过执行 (select siteID from users where userID = uID) as sID.它甚至不在 IN 子查询中.
I don't understand what I'm doing wrong here. The sID thing is not supposed to be a column, but the 'alias' (what is this called?) I created by executing (select siteID from users where userID = uID) as sID. And it’s not even inside the IN subquery.
有什么想法吗?
@Roland:感谢您的评论.我有三个表，actions、users 和 sites.actions 表包含 userID 字段，该字段对应于 users 表中的条目.此表中的每个用户 (users) 都有一个 siteID.我正在尝试从 actions 表中选择最新的操作，并将它们链接到 users 和 sites 表以找出执行这些操作的人行动，以及在哪个网站上.希望这是有道理的:)
@Roland: Thanks for your comment. I have three tables, actions, users and sites. The table actions contains a userID field, which corresponds to an entry in the users table. Every user in this table (users) has a siteID.
I'm trying to select the latest actions from the actions table, and link them to the users and sites table to find out who performed those actions, and on which site. Hope that makes sense :)
推荐答案
您要么需要将其包含在子查询中:
You either need to enclose it into a subquery:
SELECT *
FROM (
SELECT userID as uID, (select siteID from users where userID = actions.userID) as sID,
FROM actions
) q
WHERE sID IN (select siteID from sites where foo = "bar")
ORDER BY
timestamp DESC
LIMIT 100
，或者，更好的是，将其重写为 JOIN
, or, better, rewrite it as a JOIN
SELECT a.userId, u.siteID
FROM actions a
JOIN users u
ON u.userID = a.userID
WHERE siteID IN
(
SELECT siteID
FROM sites
WHERE foo = 'bar'
)
ORDER BY
timestamp DESC
LIMIT 100
创建以下索引:
actions (timestamp)
users (userId)
sites (foo, siteID)
这篇关于这个 MySQL 查询有什么问题?SELECT * AS `x`，以后如何再次使用x?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！