使用新的别名列作为键来连接表

Question

当我尝试连接两个表和我的数据时，我得到一个错误，我的引用是不明确的，或者我得到了我的原始表没有别名列的顺序。

这是我第一个使用sql的项目之一，对我来说也是一个重要的学习点，所以我很感谢您的指导。

这是我的代码。

WITH 
RentDotComOnly AS
(
  SELECT 
    concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
    COUNT(clean_zip) AS "rent_count_clean_zip", 
    AVG((low_price+high_price)/2) AS "rent_avg_price", 0.85*min(low_price) AS "rent_lower_bound", 1.15*max(high_price) AS "rent_upper_bound"
  FROM 
    archived_apartments 
  WHERE 
    source_type in (29,36,316) 
    AND week between '2015-07-06' and '2015-10-12' 
    AND is_house <> 1  
    AND archived_apartments.high_price <> 0 
  GROUP BY monthlyzip
),
AllRJData AS
(
  SELECT
    concat(DATEPART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
    COUNT(clean_zip) AS "all_count_clean_zip", 
    AVG((low_price+high_price)/2) AS "all_avg_price"
  FROM 
    archived_apartments 
  WHERE 
    week between '2015-07-06' and '2015-10-12' 
    AND is_house <> 1  
  GROUP BY monthlyzip
)
SELECT 
  concat(DATE_PART(mm,archived_apartments.week),clean_zip) AS "monthlyzip",
  COUNT(archived_apartments.clean_zip) as filtered_count_clean_zip, 
  RentDotComOnly.rent_count_clean_zip, RentDotComOnly.rent_avg_price, RentDotComOnly.rent_lower_bound, RentDotComOnly.rent_upper_bound,
  AllRjData.all_count_clean_zip, AllRjData.all_avg_price
FROM
archived_apartments 
JOIN AllRJData 
ON concat(DATE_PART(mm,archived_apartments.week),archived_apartments.clean_zip) = AllRJData.monthlyzip
JOIN RentDotComOnly
ON concat(DATE_PART(mm,archived_apartments.week),archived_apartments.clean_zip) = RentDotComOnly.monthlyzip
WHERE 
  archived_apartments.week between '2015-07-06' and '2015-10-12'
  AND archived_apartments.is_house <> 1 
  AND archived_apartments.high_price <> 0 
  AND archived_apartments.low_price > RentDotComOnly.rent_lower_bound
  OR archived_apartments.high_price < RentDotComOnly.rent_upper_bound
  AND archived_apartments.week between '2015-07-06' and '2015-10-12'
  AND archived_apartments.is_house <> 1 
  AND archived_apartments.high_price <> 0 
GROUP BY monthlyzip
RentDotComOnly.rent_count_clean_zip, RentDotComOnly.rent_avg_price, RentDotComOnly.rent_lower_bound, RentDotComOnly.rent_upper_bound, 
AllRjData.all_count_clean_zip, AllRjData.all_avg_price
ORDER BY monthlyzip

Answer 1

问题是您在连接中引用的monthlyzip列是不明确的。您有三个包含该列的表，您指的是哪个表？

如果archived_apartments上存在monthly_zip，您可以这样做：

FROM
archived_apartments JOIN
AllRJData 
ON archived_apartments.monthlyzip = AllRJData.monthlyzip
JOIN RentDotComOnly 
ON archived_apartments.monthlyzip = RentDotComOnly.monthlyzip

但是听起来archived_apartments中并不存在monthly_zip，所以你不能真正加入这个领域。相反，你必须加入你用来制作monthlyzip的公式

FROM
archived_apartments JOIN
AllRJData 
ON concat(DATEPART(mm,archived_apartments.week),archived_apartment.clean_zip)  = AllRJData.monthlyzip
JOIN RentDotComOnly 
ON concat(DATEPART(mm,archived_apartments.week),archived_apartment.clean_zip)  = RentDotComOnly.monthlyzip