MySQL Error 1054求助：关联查询SQL修正方案咨询

The error happens because you’ve aliased your filtered price_change subquery as T, but you’re still trying to reference the original price_change table in the JOIN condition. Once you assign an alias to a subquery, you must use that alias to refer to its columns instead of the original table name.

Here’s the corrected query:

SELECT 
    T.listing_id,
    (AVG((T.new_price - T.old_price)/T.old_price)) / bua.built_area AS price_change_per_sqft
FROM (
    SELECT listing_id, new_price, old_price 
    FROM price_change 
    WHERE YEAR(change_date) = 2016 AND new_price > old_price
) AS T 
JOIN built_used_area AS bua ON T.listing_id = bua.listing_id
GROUP BY T.listing_id, bua.built_area;

Key Improvements & Fixes: #

• Replaced price_change.listing_id with T.listing_id in the JOIN clause (since T is the alias for your filtered subset of price_change)
• Added a clear alias (bua) for the built_used_area table to make the query more readable
• Included GROUP BY to calculate the average price change per individual listing (divided by its corresponding built area). If you instead want the overall average across all qualifying records divided by each listing’s area, remove the GROUP BY clause and T.listing_id from the SELECT statement.

Why This Works: #

When you create a subquery with an alias like AS T, MySQL treats that subquery as a temporary table named T. Any references to columns from that subquery must use the alias T—the original table name price_change is no longer valid in that context.

内容的提问来源于stack exchange，提问作者Eldiias Dzhamankulov