我可以在 SELECT 查询中重复使用计算字段吗?

发布于 2024-11-08 18:18:37 字数 367 浏览 15 评论 0原文

有没有办法在 mysql 语句中重用计算字段。我收到错误“未知列total_sale”:

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 程序中完成所有计算。

Is there a way to reuse a calculated field within a mysql statement. I get the error "unknown column total_sale" for:

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.

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.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

家住魔仙堡 2024-11-15 18:18:37

是的,您可以重用变量。您可以这样做:

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

在此处阅读更多相关信息:http: //dev.mysql.com/doc/refman/5.0/en/user-variables.html

[注意:此行为未定义。根据 MySQL 文档:]

作为一般规则,您永远不应该为用户变量赋值并在同一语句中读取该值。您可能会得到预期的结果,但这并不能保证。

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.

同展鸳鸯锦 2024-11-15 18:18:37

在我对 MySQL 5.5 的测试中,以下内容似乎运行良好:

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

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
情痴 2024-11-15 18:18:37

唯一支持跨平台的方法是使用派生表/内联视图:

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

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
晨曦÷微暖 2024-11-15 18:18:37

您可以使用子选择:

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 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;
给我一枪 2024-11-15 18:18:37

您可以使用子查询,如下所示:

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

编辑:
修复了笛卡尔积,假设主键是id
这应该相当于 OMG Ponies 优化后的解决方案,但我认为如果你需要更多的子查询,它会变得更难阅读。

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.

与君绝 2024-11-15 18:18:37

我在这里查看了各种答案并做了一些实验。

具体来说,我正在使用 MariaDB 10.1。

对于“简单”的事情,您可以执行 Robert D 在他的评论中建议的操作:

SELECT Price_Per_SqFt, (Price_Per_SqFt/2) AS col1, (SELECT col1 + 1) AS col2 FROM Items 

如果您使用某种带有内部联接的聚合函数,则不能使用此方法,但您可以将此方法与内部联接方法结合起来,如下所示(NB VAT =“销售税”...财务数据货币字段中的 NB 通常有 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

我想使用上面的内容创建一个 View 来列出发票及其小计、增值税和总计...事实证明,MariaDB(几乎肯定还有 MySQL)不允许在 FROM 子句中嵌套。但是,通过创建第一个列出 InvoiceNoSubtotalView,然后创建第二个 View,可以轻松解决此问题> 其中引用了第一个。在性能方面,我对这种双View排列完全不了解。

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.

戏舞 2024-11-15 18:18:37

我一直在测试以下内容,它似乎一直有效,也许这是有原因的,是因为我已将变量 @total_sales 预定义为值而不是字符串,并且在选择期间没有重新定义其类型陈述 ??

如果我执行以下操作

    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 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
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文