mysql - 尝试在 where 子句中使用别名列名时出错

Question

我的 SQL 查询有问题。我想按债务金额选择记录。金额按两个表的关系统计。

这很好用:

SELECT `i`.*, 
(i.amount_netto + (i.amount_netto * i.vat / 100)) - (SUM(p.amount_netto)) AS `debt`,
`e`.`name` AS `user_name`,
`e`.`surname` AS `user_surname`,
`c`.`name` AS `contractor_name`
FROM `invoices` AS `i` 
INNER JOIN `payments` AS `p` ON i.id = p.invoice_id 
INNER JOIN `employees` AS `e` ON i.employee_id = e.id 
INNER JOIN `contractors` AS `c` ON i.contractor_id = c.id 
GROUP BY `i`.`id` 
ORDER BY `debt` ASC

但是当我通过 debt 添加 WHERE 子句时，我得到一个错误:

Unknown column 'debt' in 'where clause'

查询看起来像这样:

SELECT `i`.*, 
(i.amount_netto + (i.amount_netto * i.vat / 100)) - (SUM(p.amount_netto)) AS `debt`, 
`e`.`name` AS `user_name`,
`e`.`surname` AS `user_surname`,
`c`.`name` AS `contractor_name`
FROM `invoices` AS `i` 
INNER JOIN `payments` AS `p` ON i.id = p.invoice_id 
INNER JOIN `employees` AS `e` ON i.employee_id = e.id 
INNER JOIN `contractors` AS `c` ON i.contractor_id = c.id 
WHERE `debt` > 1 
GROUP BY `i`.`id` 
ORDER BY `debt` ASC

为什么我不能在 WHERE 子句中访问 debt，但我可以在 ORDER 子句中访问？

Answer 1

debt 不是列，而是一个别名。列(表达式)别名在查询执行后才会解析，因此不能在 WHERE 子句中使用。

例如这个查询是不合法的:

select foo + 3 as bar
from baz
where bar = 39

相反，您必须重写 WHERE 子句中的整个表达式:

select foo + 3 as bar
from baz
where foo + 3 = 39

---

此外，由于 debt 实际上是基于聚合的，因此您无法在 WHERE 子句中对其进行过滤。您必须改为使用 HAVING 来评估谓词在聚合之后。因此，您的查询应该是:

SELECT ...
FROM ...
GROUP BY ...
HAVING (i.amount_netto + (i.amount_netto * i.vat / 100)) - (SUM(p.amount_netto)) > 1
ORDER BY ...

请注意 MySql offers an SQL extension which enables the use of aliases in the HAVING clause , 所以你也可以这样做:

SELECT ...
FROM ...
GROUP BY ...
HAVING debt > 1
ORDER BY ...