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

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 where clause the query fails.

当我运行查询而没有where eclause但是当我添加where子句查询失败时，我将得到我想要的结果。

Does anyone have a suggestion how to solve that?

有人有什么建议吗?

2 个解决方案

#1

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

在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 HAVING clause instead

或者你可以用have子句代替这个条件

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

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

另一种方法是使用交叉连接并将条件应用到WHERE子句中

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

Here is SQLFiddle demo for all above mentioned queries.

这里是上面提到的所有查询的sql小提琴演示。

#2

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