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

2021-11-20 00:00:00 sql mysql mysql-error-1054

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

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

或者我是否必须重复计算，如果我添加了我需要的所有计算，这将导致很长的 SQL 语句.

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

当然我可以在我的php程序中完成所有的计算.

of course I can do all the calculations in my php program.

推荐答案

是的，您可以重用变量.这就是你的做法:

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

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

Read more about it here: http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

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

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

相关文章