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

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