SQL在where子句中使用子选择中的列

2022-01-23 00:00:00 sql where-clause subquery mysql

我有一个类似这样的查询:

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

当我在没有 where 子句的情况下运行查询时，我得到了我想要的结果，但是当我添加 where 子句时，查询失败.

I get the result that I want when I run the query without the whereclause but when I add the where clause the query fails.

有没有人建议如何解决这个问题?

Does anyone have a suggestion how to solve that?

推荐答案

不能在 WHERE 子句中使用列别名.

You can't use a column alias in WHERE clause.

因此，您可以将查询包装在外部选择中并在那里应用您的条件

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

或者您可以在 HAVING 子句中引入该条件

or you can introduce that condition in HAVING clause instead

SELECT a, b, c,
    (SELECT d FROM B LIMIT 0,1) d
  FROM A
HAVING d >= 10

另一种方法是使用 CROSS JOIN 并在 WHERE 子句中应用您的条件

Yet another approach is to use CROSS JOIN and apply your condition in WHERE clause 

SELECT a, b, c, d
  FROM A CROSS JOIN 
(
  SELECT d FROM B LIMIT 0,1
) q
 WHERE d >= 10

这里是所有上述查询的 SQLFiddle 演示.

Here is SQLFiddle demo for all above mentioned queries.

相关文章