这个 MySQL 查询有什么问题?SELECT * AS `x`，以后如何再次使用x?

2022-01-23 00:00:00 sql subquery mysql in-subquery

以下 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)

相关文章