这个MySQL查询有什么问题? SELECT * AS`x`，以后如何再次使用x? [英] What's wrong with this MySQL query? SELECT * AS `x`, how to use x again later?

问题描述

以下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子查询中.

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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！