这个MySQL查询有什么问题?选择*作为“x”，以后如何再次使用x ?

The following MySQL query:

以下MySQL查询:

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

…returns an error:

…返回一个错误:

Unknown column 'sID' in 'IN/ALL/ANY subquery'

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.

我不明白我在这里做错了什么。sID项不应该是列，而应该是“alias”(这叫什么?)我通过执行(从userID = uID的用户中选择siteID)作为sID创建。它甚至不在IN子查询中。

Any ideas?

什么好主意吗?

Edit: @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 :)

编辑:@Roland:谢谢你的评论。我有三个表，动作，用户和站点。表动作包含一个userID字段，该字段对应于users表中的一个条目。该表中的每个用户(用户)都有一个siteID。我正在尝试从动作表中选择最新的操作，并将它们链接到用户和站点表，以找出谁执行了这些操作，以及在哪个站点上。希望这是有道理的

5 个解决方案

#1

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

, 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

Create the following indexes:

创建以下指标:

actions (timestamp)
users (userId)
sites (foo, siteID)

#2

The column alias is not established until the query processor finishes the Select clause, and buiulds the first intermediate result set, so it can only be referenced in a group By, (since the group By clause operates on that intermediate result set) if you want ot use it this way, puit the alias inside the sub-query, then it will be in the resultset generated by the subquery, and therefore accessible to the outer query. To illustrate

列别名才建立查询处理器完成Select子句,和buiulds第一个中间结果集,所以它只能引用一组,(因为group By子句的中间结果集的操作)如果你想不能这样使用它,冶金部别名在子查询,那么它将子查询生成的结果集,因此可以访问外部查询。为了说明

(This is not the simplest way to do this query but it illustrates how to establish and use a column alias from a subquery)

(这不是执行此查询的最简单方法，但它演示了如何从子查询中建立和使用列别名)

 select a.userID as uID, z.Sid
 from actions a
 Join  (select userID, siteID as sid1 from users) Z, 
     On z.userID = a.userID
 where Z.sID in (select siteID from sites where foo = "bar") 
 order by timestamp desc limit 100

#3

Try the following:

试试以下:

SELECT
       a.userID as uID
       ,u.siteID as sID
    FROM
       actions as a
    INNER JOIN
       users as u ON u.userID=a.userID
    WHERE
       u.siteID IN (SELECT siteID FROM sites WHERE foo = 'bar')
    ORDER BY
       a.timestamp DESC
    LIMIT 100

#4

I think the reason for the error is that the alias isn't available to the WHERE instruction, which is why we have HAVING.

我认为错误的原因是别名对WHERE指令不可用，这就是为什么我们有。

select `userID` as uID,
(select `siteID` from `users` where `userID` = uID) as `sID`,
from `actions`
HAVING `sID` in (select `siteID` from `sites` where `foo` = "bar")
order by `timestamp` desc limit 100

Though i also agree with the other answers that your query could be better structured.

虽然我也同意其他的回答，您的查询可以有更好的结构。

#5

Try the following

试试以下

SELECT 
       a.userID as uID 
       ,u.siteID as sID 
    FROM 
       actions as a 
    INNER JOIN 
       users as u ON u.userID = a.userID 
    INNER JOIN
        sites as s ON u.siteID = s.siteID   
    WHERE 
       s.foo = 'bar'
    ORDER BY 
       a.timestamp DESC 
    LIMIT 100

If you wish to use a field from the select section later you can try a subselect

如果您希望以后使用select部分中的字段，您可以尝试子select

SELECT  One,
        Two,
        One + Two as Three
FROM    (
            SELECT  1 AS One,
                    2 as Two
        ) sub

#1