我可以在 SELECT 查询中重用计算字段吗？

在 mysql 语句中是否有重用计算字段的方法。我得到了“未知列 total _ sales”的错误:

SELECT
s.f1 + s.f2 as total_sale,
s.f1 / total_sale as f1_percent
FROM sales s

或者我必须重复计算，如果我添加所有我需要的计算，这将导致一个非常长的 SQL 语句。

SELECT
s.f1 + s.f2 as total_sale,
s.f1 / (s.f1 + s.f2) as f1_percent
FROM sales s

当然我可以在我的 php 程序中做所有的计算。

59711 次浏览
小开
最佳答案

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

[Note: This behavior is undefined. According to the MySQL docs:]

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.

小开

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;
小开

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.

小开

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
小开

The following seems to work well in my testing on MySQL 5.5:

SELECT
s.f1 + s.f2 as total_sale,
s.f1 / (SELECT total_sale) as f1_percent
FROM sales s
小开

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 ??

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
小开

I had a look at various answers here and did a few experiments.

Specifically I am using MariaDB 10.1.

For a "simple" thing you can do what Robert D suggested in his comment:

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...)

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 View to list invoices with their subtotals, VAT and totals... it turned out that MariaDB (and almost certainly MySQL) don't allow nesting in the FROM clause. However this is easily solved by making a first View which lists the InvoiceNo and Subtotal, and then making a second View which references the first. Performance-wise I have no idea at all about this sort of double-View arrangement.


提交答案