where子句中subselect的SQL使用列 [英] SQL use column from subselect in where clause

问题描述

我有一个看起来像这样的查询:

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.

有人建议如何解决吗?

推荐答案

您不能在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.

这篇关于where子句中subselect的SQL使用列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！