postgresql - 列引用不明确

Question

我不断收到以下代码的“错误:列引用“delta_hh_2010to2011”不明确”:

有人会碰巧知道我是否遗漏了什么或消除该列的歧义吗？谢谢!

DROP TABLE IF EXISTS us2011;
CREATE TABLE us2011 AS (
SELECT stateabbr, block_fips, hu2010, hh2010, pop2010
--    , hu2010+delta_hu_2010to2011 as hu2011, greatest(0,hh2010+delta_hh_2010to2011) as hh2011, pop2010+delta_pop_2010to2011 as pop2011
  , hu2010 + delta_hu_2010to2011  as hu2011, greatest(0, hh2010 + delta_hh_2010to2011) as hh2011, pop2010 + delta_pop_2010to2011 as pop2011 
--    , hu2011+delta_hu_2011to2012  as hu2012,greatest(0,hh2011+delta_hh_2011to2012) as hh2012, pop2011+delta_pop_2011to2012 as pop2012 
--    , hu2011+delta_hu_2010to2011+delta_hu_2011to2012  as hu2012, greatest(0,hh2011+delta_hh_2011to2012) as hh2012, pop2011+delta_pop_2011to2012 as pop2012 
FROM (
SELECT  upper(stateabbr) as stateabbr, block_fips, hu2010, hh2010, pop2010
,sum(coalesce(CASE WHEN startyear=2010 THEN new_road_hu   END,0))+sum(coalesce(CASE WHEN startyear=2010 THEN removed_hu END,0))
+sum(coalesce(CASE WHEN startyear=2010 THEN added_hu END,0)) as delta_hu_2010to2011
 -- , CASE WHEN hu2010>0 THEN hh2010::numeric/hu2010::numeric END, county_hh_ratio 
 -- , CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010::numeric/hu2010::numeric END 
, round(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010::numeric/hu2010::numeric END * (sum(coalesce(new_road_hu,0))+ sum(coalesce(removed_hu,0))+ sum(coalesce(added_hu,0))),4) as delta_hh_2010to2011
, sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))*(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010/hu2010::numeric END * sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))) as delta_hh_2010to2011
, sum(coalesce(new_road_pop,0))+sum(coalesce(removed_pop,0))+sum(coalesce(added_pop,0)) as delta_pop_2010to2011
FROM us2010 LEFT JOIN counts.new_counts USING (block_fips) LEFT JOIN county_hh_per_hu ON left(block_fips,5)=county_fips
GROUP BY block_fips, stateabbr, hu2010, hh2010, pop2010, county_hh_ratio
) foo);

抱歉代码太长了!再次感谢!

Answer 1

你使用同一个别名两次:

, round(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010::numeric/hu2010::numeric END * (sum(coalesce(new_road_hu,0))+ sum(coalesce(removed_hu,0))+ sum(coalesce(added_hu,0))),4) as delta_hh_2010to2011
, sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))*(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010/hu2010::numeric END * sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))) as delta_hh_2010to2011

改为

, round(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010::numeric/hu2010::numeric END * (sum(coalesce(new_road_hu,0))+ sum(coalesce(removed_hu,0))+ sum(coalesce(added_hu,0))),4) 
as delta_hh_2010to2011
, sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))*(CASE WHEN hh2010=0 THEN county_hh_ratio ELSE hh2010/hu2010::numeric END * sum(coalesce(new_road_hu,0))+sum(coalesce(removed_hu,0))+sum(coalesce(added_hu,0))) 
as delta_hh_2010to2011_other