mysql– 使用2个别名的数学结果创建别名

概述我试图减去2个别名,以便创建另一个别名,但我得到一个“未知列”错误.这是我的SQL：select o.id, o.name, (select sum(l.source_expense) from `assignments` as a left join `leads` as l on (l.id = a.lead_i

我试图减去2个别名,以便创建另一个别名,但我得到一个“未知列”错误.

这是我的sql：

select o.ID,o.name,(select sum(l.source_expense)        from `assignments` as a        left join `leads` as l on (l.ID = a.lead_ID)        where a.{$this->sql_column}=o.ID        and l.date_created between {$this->date_from} and {$this->date_to}        and find_in_set(l.vertical_ID,'".implode(',',$this->app_user->verticals)."')     ) as `expense`,(select sum(a.buyer_revenue)        from `assignments` as a        left join `leads` as l on (l.ID = a.lead_ID)        where a.refunded=0        and a.{$this->sql_column}=o.ID        and l.date_created between {$this->date_from} and {$this->date_to}        and find_in_set(l.vertical_ID,$this->app_user->verticals)."')     ) as `revenue`,`revenue` - `expense` as `profit`     from {$this->sql_table} as o

基本上,我想通过从费用中减去收入来创建利润别名.原因是我正在使用数据表并希望列可以排序.我已经知道我可以用PHP轻松做到这一点.

我怎么能做到这一点？

编辑 – 我尝试了下面的答案,并从PHPStorm获得“每个派生表应该有别名”错误,并在尝试运行查询时出现语法错误.

继承人新的查询：

select t.ID,t.name,t.expense,t.revenue,t.revenue - t.expense as profitfrom(select o.ID,$this->app_user->verticals)."')     ) as `revenue`     from {$this->sql_table} as o ) as t

@H_404_26@最佳答案只需用另一个select包装它,然后别名将可用于数学计算：

SELECT t.ID,t.revenue -t.expense as `profit`FROM (Your query Here) t

总结

以上是内存溢出为你收集整理的mysql – 使用2个别名的数学结果创建别名全部内容，希望文章能够帮你解决mysql – 使用2个别名的数学结果创建别名所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。