我可以在 SELECT 查询中重复使用计算字段吗?
有没有办法在 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 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(7)
是的,您可以重用变量。您可以这样做:
在此处阅读更多相关信息:http: //dev.mysql.com/doc/refman/5.0/en/user-variables.html
[注意:此行为未定义。根据 MySQL 文档:]
Yes, you can reuse variables. This is how you do it:
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:]
在我对 MySQL 5.5 的测试中,以下内容似乎运行良好:
The following seems to work well in my testing on MySQL 5.5:
唯一支持跨平台的方法是使用派生表/内联视图:
Only cross-platform supported means is by using a derived table/inline view:
您可以使用子选择:
You can use a sub-select:
您可以使用子查询,如下所示:
编辑:
修复了笛卡尔积,假设主键是
id
。这应该相当于 OMG Ponies 优化后的解决方案,但我认为如果你需要更多的子查询,它会变得更难阅读。
You can use subqueries, like this:
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.
我在这里查看了各种答案并做了一些实验。
具体来说,我正在使用 MariaDB 10.1。
对于“简单”的事情,您可以执行 Robert D 在他的评论中建议的操作:
如果您使用某种带有内部联接的聚合函数,则不能使用此方法,但您可以将此方法与内部联接方法结合起来,如下所示(NB VAT =“销售税”...财务数据货币字段中的 NB 通常有 4 位小数,我认为这是历史性的...)
我想使用上面的内容创建一个
View
来列出发票及其小计、增值税和总计...事实证明,MariaDB(几乎肯定还有 MySQL)不允许在FROM
子句中嵌套。但是,通过创建第一个列出InvoiceNo
和Subtotal
的View
,然后创建第二个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:
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...)
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 theFROM
clause. However this is easily solved by making a firstView
which lists theInvoiceNo
andSubtotal
, and then making a secondView
which references the first. Performance-wise I have no idea at all about this sort of double-View
arrangement.我一直在测试以下内容,它似乎一直有效,也许这是有原因的,是因为我已将变量 @total_sales 预定义为值而不是字符串,并且在选择期间没有重新定义其类型陈述 ??
如果我执行以下操作
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