MySQL 我可以在 SELECT 查询中重用计算字段吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6085443/
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
Can I reuse a calculated field in a SELECT query?
提问by sdfor
Is there a way to reuse a calculated field within a mysql statement. I get the error "unknown column total_sale" for:
有没有办法在 mysql 语句中重用计算字段。我收到错误“未知列 total_sale”：
SELECT
s.f1 + s.f2 as total_sale,
s.f1 / total_sale as f1_percent
FROM sales s
or do I have to repeat the calculation, which would make for a very long SQL statement if I added all the calculations I need.
或者我是否必须重复计算，如果我添加了我需要的所有计算，这将导致很长的 SQL 语句。
SELECT
s.f1 + s.f2 as total_sale,
s.f1 / (s.f1 + s.f2) as f1_percent
FROM sales s
of course I can do all the calculations in my php program.
当然，我可以在我的 php 程序中进行所有计算。
回答by rzetterberg
Yes, you can reuse variables. This is how you do it:
是的，您可以重用变量。这是你如何做到的：
SELECT
@total_sale := s.f1 + s.f2 as total_sale,
s.f1 / @total_sale as f1_percent
FROM sales s
Read more about it here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
在此处阅读更多相关信息：http: //dev.mysql.com/doc/refman/5.0/en/user-variables.html
[Note: This behavior is undefined. According to the MySQL docs:]
[注意：此行为未定义。根据 MySQL 文档：]
As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed.
作为一般规则，您永远不应为用户变量赋值并在同一语句中读取该值。您可能会得到预期的结果，但这并不能保证。
回答by Robert D
The following seems to work well in my testing on MySQL 5.5:
以下似乎在我对 MySQL 5.5 的测试中运行良好：
SELECT
s.f1 + s.f2 as total_sale,
s.f1 / (SELECT total_sale) as f1_percent
FROM sales s
回答by OMG Ponies
Only cross-platform supported means is by using a derived table/inline view:
只有跨平台支持的方法是使用派生表/内联视图：
SELECT x.total_sale,
x.f1 / x.total_sale as f1_percent
FROM (SELECT s.f1,
s.f1 + s.f2 as total_sale,
FROM sales s) x
回答by AJ.
You can use a sub-select:
您可以使用子选择：
select tbl1.total_sale,
tbl1.f1/tbl1.total_sale as f1_percent
from (select s.f1+s.f2 AS total_sale,
s.f1
from sales s) as tbl1;
回答by kiw
You can use subqueries, like this:
您可以使用子查询，如下所示：
SELECT
h.total_sale,
s.f1 / h.total_sale AS f1_percent
FROM sales s,
(SELECT id, f1 + f2 AS total_sale FROM sales) h
WHERE
s.id = h.id
Edit:
fixed cartesian product, assuming the primary key is id.
This should be equivalent to OMG Ponies' solution after optimizing, but I think it will become harder to read if you need more subqueries.
编辑：
固定笛卡尔积，假设主键是id.
这应该相当于优化后的OMG Ponies的解决方案，但我认为如果您需要更多子查询，它会变得更难阅读。
回答by mike rodent
I had a look at various answers here and did a few experiments.
我在这里查看了各种答案并做了一些实验。
Specifically I am using MariaDB 10.1.
具体来说，我使用的是 MariaDB 10.1。
For a "simple" thing you can do what Robert D suggested in his comment:
对于“简单”的事情，您可以按照 Robert D 在评论中的建议进行操作：
SELECT Price_Per_SqFt, (Price_Per_SqFt/2) AS col1, (SELECT col1 + 1) AS col2 FROM Items
If you are using some sort of aggregate function with an inner join you can't use this, but you can combine this approach with the inner join approach as follows (NB VAT = "sales tax"... and NB in financial data currency fields typically have 4 decimal places, I think it's historic...)
如果您使用某种带有内连接的聚合函数，则不能使用此方法，但您可以将这种方法与内连接方法相结合，如下所示（NB VAT = "sales tax"... 和 NB infinancial data currency字段通常有 4 个小数位，我认为这是历史性的......）
SELECT
invoices.invoiceNo, invoices.clientID, invoices.Date, invoices.Paid,
invoicesWithSubtotal.Subtotal,
ROUND( CAST( Subtotal * invoices.VATRate AS DECIMAL( 10, 4 )), 2 ) AS VAT,
(SELECT VAT + Subtotal) AS Total
FROM invoices
INNER JOIN
( SELECT Sum( invoiceitems.Charge ) AS Subtotal, invoices.InvoiceNo FROM invoices
INNER JOIN invoiceitems ON invoices.InvoiceNo = invoiceitems.InvoiceNo
GROUP BY invoices.InvoiceNo ) invoicesWithSubtotal
ON invoices.InvoiceNo = invoicesWithSubtotal.InvoiceNo
I wanted to use the above to create a Viewto list invoices with their subtotals, VAT and totals... it turned out that MariaDB (and almost certainly MySQL) don't allow nesting in the FROMclause. However this is easily solved by making a first Viewwhich lists the InvoiceNoand Subtotal, and then making a second Viewwhich references the first. Performance-wise I have no idea at all about this sort of double-Viewarrangement.
我想使用上面的内容来创建一个View列出发票及其小计、增值税和总计......结果是 MariaDB（几乎可以肯定是 MySQL）不允许在FROM子句中嵌套。然而，这很容易通过创建一个View列出InvoiceNoand的第一个来解决Subtotal，然后创建一个View引用第一个的第二个。性能方面我完全不知道这种双重View安排。
回答by Lawrence Edel
I have been testing the following and it seems to work all the time, maybe there is a reason for this, is it because I have predefined the variable @total_sales as being a value not a string, and have not redefined its type during the select statement ??
我一直在测试以下内容，它似乎一直都在工作，也许这是有原因的，是因为我已将变量 @total_sales 预定义为值而不是字符串，并且在选择期间没有重新定义其类型陈述 ？？
If I do the following
如果我执行以下操作
set @total_sales = 0;
SELECT
@total_sale := s.f1 + s.f2 as total_sale,
s.f1 / @total_sale as f1_percent
FROM sales s
