MySQL SQL 在 where 子句中使用来自 subselect 的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18029786/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL use column from subselect in where clause
提问by Chris
I have a query that looks something like that:
我有一个看起来像这样的查询：
SELECT a, b, c,
(SELECT d from B limit 0,1) as d
FROM A
WHERE d >= 10
I get the result that I want when I run the query without the whereclause but when I add the whereclause the query fails.
当我在没有where子句的情况下运行查询时，我得到了我想要的结果，但是当我添加where子句时，查询失败。
Does anyone have a suggestion how to solve that?
有没有人有建议如何解决这个问题？
回答by peterm
You can't use a column alias in WHEREclause.
您不能在WHERE子句中使用列别名。
So you either wrap your query in an outer select and apply your condition there
因此，您可以将查询包装在外部选择中并在那里应用您的条件
SELECT *
FROM
(
SELECT a, b, c,
(SELECT d FROM B LIMIT 0,1) d
FROM A
) q
WHERE d >= 10
or you can introduce that condition in HAVINGclause instead
或者您可以在HAVING子句中引入该条件
SELECT a, b, c,
(SELECT d FROM B LIMIT 0,1) d
FROM A
HAVING d >= 10
Yet another approach is to use CROSS JOINand apply your condition in WHEREclause
另一种方法是CROSS JOIN在WHERE子句中使用和应用您的条件
SELECT a, b, c, d
FROM A CROSS JOIN
(
SELECT d FROM B LIMIT 0,1
) q
WHERE d >= 10
Here is SQLFiddledemo for all above mentioned queries.
这是所有上述查询的SQLFiddle演示。
回答by Clxy
Is this what you want?
这是你想要的吗？
SELECT a, b, c,
B.d
FROM A, (SELECT d from B limit 0,1) B
WHERE B.d >= 10
