对 mysql 中的列求和然后使用 where 子句中的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15618812/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sum columns in mysql then use the result in where clause
提问by MichelReap
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
But I am getting an unknown column on using "sumTotal".
但是我收到了一个关于使用“sumTotal”的未知专栏。
Is this possible?
这可能吗？
回答by Haralan Dobrev
Use HAVING:
使用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
The problem is that the WHEREclause is executedbefore the SELECTstatement. Therefore the sumTotalcolumn is not yet available.
问题是WHERE子句在语句之前执行SELECT。因此，该sumTotal列尚不可用。
The HAVINGclause is executedafter the SELECTstatement. It kinds of filter the results out after you have selected everything. Bear in mind, though, because of that using HAVINGis slower. It operates on the whole set of rows.
该HAVING条款被执行后SELECT声明。在您选择所有内容后，它会过滤结果。但请记住，因为使用HAVING速度较慢。它对整个行集进行操作。
From the MySQL documentation:
从MySQL 文档：
The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)
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 子句几乎最后应用，就在项目被发送到客户端之前，没有优化。（LIMIT 在 HAVING 之后应用。）
SQL 标准要求 HAVING 必须仅引用 GROUP BY 子句中的列或聚合函数中使用的列。但是，MySQL 支持此行为的扩展，并允许 HAVING 引用 SELECT 列表中的列和外部子查询中的列。
The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:
HAVING 子句可以引用聚合函数，而 WHERE 子句不能：
SELECT user, MAX(salary)
FROM users
GROUP BY user
HAVING MAX(salary) > 10;
Do not use HAVING for items that should be in the WHERE clause.
不要对应该在 WHERE 子句中的项目使用 HAVING。
回答by Strawberry
Or use WHERE...
或者使用 WHERE...
SELECT dept.id, (invoices.col1 + invoices.col2 + invoices.col3) as sumTotal
FROM dept
JOIN invoices ON invoices.id_dept = dept.id
WHERE invoices.col1 + invoices.col2 + invoices.col3 > 10000
回答by user1994514
You can also try this:
你也可以试试这个：
WITH temp AS
(
SELECT
dept.id,
(invoices.col1 + invoices.col2 + invoices.col3) as sumTotal
FROM dept INNER JOIN invoices ON invoices.id_dept = dept.id
)
SELECT *
FROM temp
WHERE sumTotal > 10000
回答by No'am Newman
You have to use '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
I'm not sure whether you can use your aliased field or whether you have to write 'having (invoices.col1 + invoices.col2 + invoices.col3) > 10000.
我不确定您是否可以使用别名字段，或者是否必须写“具有 (invoices.col1 + invoices.col2 + invoices.col3) > 10000”。
The 'where' statement works in conjunction with the 'select': it filters which records are initially returned. 'Having' then filters that returned dataset, normally because the 'having' condition could not be known at the time of the 'select'.
'where' 语句与 'select' 结合使用：它过滤最初返回的记录。“拥有”然后过滤返回的数据集，通常是因为在“选择”时无法知道“拥有”条件。
To quote one documentation, 'The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.' Somewhere else is written 'The SQL HAVING clause is used in combination with the SQL GROUP BY clause. It can be used in an SQL SELECT statement to filter the records that a SQL GROUP BY returns.'
引用一份文档，“HAVING 子句被添加到 SQL 中，因为 WHERE 关键字不能与聚合函数一起使用。” 在别处写成 ' SQL HAVING 子句与 SQL GROUP BY 子句结合使用。它可以在 SQL SELECT 语句中用于过滤 SQL GROUP BY 返回的记录。
