select r.id, r.name,
       sum(p.crop_area) as crop_area_sum,
       sum(p.total_gross) as total_gross_sum
       total_gross_sum*10/crop_area_sum as total_normative
from placement p
right join region r on p.region_id = r.id
where r.region_type = 2 and r.parent_id = 255
group by r.id;

在上面的代码中，我想使用我得到的变量crop_area和gross_product，而不需要再次求和来找到total_normal。但它给出了一个错误。enter image description here

select r.id, r.name,
       sum(p.crop_area) as crop_area_sum,
       sum(p.total_gross) as total_gross_sum,
       cast(sum(p.total_gross)*10/sum(p.crop_area) as decimal(16, 2)) as total_normative
from placement p
right join region r on p.region_id = r.id
where r.region_type = 2 and r.parent_id = 255
group by r.id;

如果我按照上面的格式写，它就能正常工作。但由于我有很多列是这样计算的，所以我重新求和它们，所以我想使用之前获得的变量crop_area_sum和total_gross_sum。