代码之家 › 专栏 › 技术社区 › Mathias Bynens

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

in-subquery subquery mysql sql

Mathias Bynens · 技术社区 · 15 年前

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

返回一个错误:

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

我不明白我在这里做错了什么。这个 sID 不应该是列,而是“别名”(这叫什么?)我是通过执行 (select siteID from users where userID = uID) as sID . 它甚至不在 IN 子查询。

有什么想法吗?

编辑: @罗兰:谢谢你的评论。我有三张桌子, actions , users 和 sites . 桌子 行动 包含一个 userID 字段,对应于 用户 表。此表中的每个用户( 用户 有A siteID . 我正在尝试从 行动 表,并将它们链接到 用户 和 地点 表以确定谁执行了这些操作,以及在哪个站点上执行了这些操作。希望这是有道理的:)

5 回复 | 直到 14 年前

Quassnoi 15 年前

您要么需要将其放入子查询中:

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

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)

Charles Bretana 15 年前

在查询处理器完成select子句并构建第一个中间结果集之前,不会建立列别名,因此它只能在group by中引用(因为group by子句对该中间结果集进行操作),如果不想这样使用它,请将别名放在子查询中,然后将其放在结果中。ET由子查询生成,因此外部查询可以访问。举例说明

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

 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

mikecsh 15 年前

尝试以下操作:

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

Question Mark 15 年前

我认为产生错误的原因是别名对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

尽管我也同意其他的答案,您的查询可以更好地结构化。

Adriaan Stander 15 年前

尝试以下操作

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

如果要使用“选择”部分中的字段后来您可以尝试一个嵌套select

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