执行SQL查询时为何提示WHERE子句中monthly_usage列不存在？

Great question! The issue here boils down to SQL's execution order—the database doesn't recognize the monthly_usage alias when it processes the WHERE clause. Let me break this down clearly:

The Root Cause #

SQL executes clauses in a strict sequence, and it goes like this:

1. FROM / JOIN (fetches and combines data from tables)
2. WHERE (filters rows before any column calculations)
3. GROUP BY (groups rows if needed)
4. HAVING (filters grouped results)
5. SELECT (calculates columns and assigns aliases)
6. ORDER BY (sorts the final output)

Your monthly_usage alias is defined in the SELECT clause, which runs after the WHERE clause. So when the database tries to check monthly_usage <> 0 in WHERE, that alias hasn't been created yet—hence the "unknown column" error.

Fixes to Try #

Here are three practical ways to resolve this:

1. Repeat the Subquery in the WHERE Clause #

You can copy the subquery that calculates monthly_usage directly into the WHERE condition. This works because the database executes the subquery here (before the SELECT phase):

SELECT 
    c.remaining_budget, 
    c.future_liabilities, 
    c.used_this_month, 
    bi.full_name, 
    bi.id AS item_id, 
    (SELECT Sum(amount) FROM balance_histories WHERE balance_histories.budget_item_id = 21 AND Date_format(payment_date, '%Y-%m-01') = '2018-01-01') AS monthly_usage 
FROM families_budget_items_calcs c 
LEFT JOIN budget_items bi ON c.budget_item_id = bi.id 
WHERE 
    c.family_id = 54824 
    AND bi.id IN (21) 
    AND (SELECT Sum(amount) FROM balance_histories WHERE balance_histories.budget_item_id = 21 AND Date_format(payment_date, '%Y-%m-01') = '2018-01-01') <> 0

Note: Most modern databases will optimize this to avoid running the subquery twice, but it does make your code a bit repetitive.

2. Wrap the Query in a Subquery #

Use an inner subquery to calculate monthly_usage first, then filter on the alias in the outer query. This keeps your code DRY (Don't Repeat Yourself):

SELECT *
FROM (
    SELECT 
        c.remaining_budget, 
        c.future_liabilities, 
        c.used_this_month, 
        bi.full_name, 
        bi.id AS item_id, 
        (SELECT Sum(amount) FROM balance_histories WHERE balance_histories.budget_item_id = 21 AND Date_format(payment_date, '%Y-%m-01') = '2018-01-01') AS monthly_usage 
    FROM families_budget_items_calcs c 
    LEFT JOIN budget_items bi ON c.budget_item_id = bi.id 
    WHERE 
        c.family_id = 54824 
        AND bi.id IN (21)
) AS budget_subquery
WHERE monthly_usage <> 0

3. Use a CTE (Common Table Expression) #

If your database supports CTEs (like MySQL 8+, PostgreSQL, SQL Server, etc.), this is the cleanest approach. CTEs act as temporary tables that you can reference later:

WITH budget_calculations AS (
    SELECT 
        c.remaining_budget, 
        c.future_liabilities, 
        c.used_this_month, 
        bi.full_name, 
        bi.id AS item_id, 
        (SELECT Sum(amount) FROM balance_histories WHERE balance_histories.budget_item_id = 21 AND Date_format(payment_date, '%Y-%m-01') = '2018-01-01') AS monthly_usage 
    FROM families_budget_items_calcs c 
    LEFT JOIN budget_items bi ON c.budget_item_id = bi.id 
    WHERE 
        c.family_id = 54824 
        AND bi.id IN (21)
)
SELECT * FROM budget_calculations WHERE monthly_usage <> 0

All three methods will fix your error—pick the one that best fits your database version and code style preferences!

内容的提问来源于stack exchange，提问作者Noam B.