在 WHERE 子句中使用子查询结果列时的未知列 [英] Unknown column when using subquery result column in WHERE clause
问题描述
我要加入几个表,以便使用 COUNT()
选择总计列.此查询非常适合返回 total
列:
I have a few tables I'm joining to select a column for totals using COUNT()
. This query works great with returning the total
column:
SELECT *,`excel`.`drivers`.`AutoNumber` AS `current_driver`,
(SELECT COUNT(`issues`.`categories`.`points`) FROM `issues`.`points` INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id` WHERE `excel`.`drivers`.`AutoNumber`=`current_driver`) AS `total`
FROM `issues`.`points`
INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber`
INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id`
但现在我正在尝试创建一个查询来比较 WHERE 子句中的 total
,如下所示:
But now I'm trying to create a query that compares total
in there WHERE clause, like this:
SELECT *,`excel`.`drivers`.`AutoNumber` AS `current_driver`,
(SELECT COUNT(`issues`.`categories`.`points`) FROM `issues`.`points` INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id` WHERE `excel`.`drivers`.`AutoNumber`=`current_driver`) AS `total`
FROM `issues`.`points`
INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber`
INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id` WHERE `total` = 3
这会返回一个错误,指出 total
是一个未知列.有什么想法吗?
This returns with an error that total
is an unknown column. Any ideas?
推荐答案
列别名不能在 WHERE
子句中使用.试试
Column aliases cannot be used in WHERE
clause. Try
select a.*
FROM
(
SELECT [column_list]
,`excel`.`drivers`.`AutoNumber` AS `current_driver`,
(SELECT COUNT(`issues`.`categories`.`points`) FROM `issues`.`points` INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber` INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id` WHERE `excel`.`drivers`.`AutoNumber`=`current_driver`) AS `total`
FROM `issues`.`points`
INNER JOIN `excel`.`drivers` ON `issues`.`points`.`driver_id` = `excel`.`drivers`.`AutoNumber`
INNER JOIN `issues`.`categories` ON `issues`.`categories`.`id` = `issues`.`points`.`category_id`
)a
WHERE a.`total` >3
您也可以在 WHERE
中重复total"的整个定义 - 智能优化器很可能会生成相同的计划,但为了可读性我不会这样做.
You can also repeat the whole definition of "total" in WHERE
- smart optimizer will very likely generate the same plan, but I wouldn't do that for readability sake.
这篇关于在 WHERE 子句中使用子查询结果列时的未知列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!