mysql中的总和列，然后在where子句中使用结果 [英] Sum columns in mysql then use the result in where clause

问题描述

我希望能够做到这一点:

I want to be able to do this:

SELECT dept.id, (invoices.col1 + invoices.col2 + invoices.col3) as sumTotal 
FROM dept 
     INNER JOIN invoices ON invoices.id_dept = dept.id 
WHERE sumTotal > 10000

但是关于"sumTotal"的使用，我收到了一个未知的专栏.

But I am getting an unknown column on using "sumTotal".

这可能吗?

推荐答案

使用HAVING:

SELECT dept.id, (invoices.col1 + invoices.col2 + invoices.col3) as sumTotal 
FROM dept 
INNER JOIN invoices
    ON invoices.id_dept = dept.id 
HAVING sumTotal > 10000

问题在于WHERE子句在SELECT语句之前被执行.因此，sumTotal列尚不可用.

The problem is that the WHERE clause is executed before the SELECT statement. Therefore the sumTotal column is not yet available.

在SELECT语句之后，执行了子句.选择所有内容后，它会过滤掉结果.但是请记住，因为使用HAVING的速度较慢.它对整个行进行操作.

The HAVING clause is executed after the SELECT statement. It kinds of filter the results out after you have selected everything. Bear in mind, though, because of that using HAVING is slower. It operates on the whole set of rows.

从 MySQL文档:

HAVING子句几乎在最后一次应用，即在将项发送到客户端之前，没有进行优化. (HAVING之后将应用限制.)

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)

SQL标准要求HAVING必须仅引用GROUP BY子句中的列或聚合函数中使用的列.但是，MySQL支持对此行为的扩展，并允许HAVING引用SELECT列表中的列以及外部子查询中的列.

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

---

HAVING子句可以引用聚合函数，而WHERE子句不能:

The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

SELECT user, MAX(salary)
FROM users
GROUP BY user
HAVING MAX(salary) > 10;

不要对应该在WHERE子句中的项目使用HAVING.

Do not use HAVING for items that should be in the WHERE clause.

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