如何从mysql中的别名列计算总和?

uqzxnwby  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(714)

我有两列:合计和百分比。我用alias创建了一个新的total列,现在我想通过求这个列和alias total列的和来获得一个percentage列。我试过的是:

SELECT sum(CASE when quality_class =1  then
    trees_ha else 0 end)/get_total_forest_plots()  +
sum(CASE when quality_class =2  then
    trees_ha else 0 end)/get_total_forest_plots()  as total ,

(sum(CASE when quality_class =1  then
     trees_ha else 0 end)/get_total_forest_plots()  +   
sum(CASE when quality_class =3  then
    trees_ha else 0 end)/get_total_forest_plots())/sum(total) *100 as percentage from (SELECT nested query from my_table);

我使用了sum(total)方法,但得到的错误是error:column“total”不存在。

zvokhttg

zvokhttg1#

是的,不能在同一级别使用别名。考虑到代码的逻辑已经在工作,您需要编写一个上层查询来使用别名,只需在这里更正使用别名的语法即可

SELECT 
   sum(CASE when quality_class =1 then trees_ha else 0 end)/get_total_forest_plots() + sum(CASE when quality_class =3 then trees_ha else 0 end)/get_total_forest_plots())/sum(initial_data.total) *1
FROM
    (SELECT 
    sum(CASE when quality_class =1 then trees_ha else 0 end)/get_total_forest_plots() + sum(CASE when quality_class =2 then trees_ha else 0 end)/get_total_forest_plots() as total 
From
<Table name> 
) initial_data
xjreopfe

xjreopfe2#

方案1

SELECT 3 x, (SELECT 1+1) y, 3-(SELECT 1+1) z;

或者
方案2

SELECT x, y, x-y z FROM
  ( SELECT 3 x, (SELECT 1+1) y) q

相关问题