SQL技术疑问：如何在JOIN ON子句中使用AS定义的计算列别名？

First, let's break down why your original query failed: SQL doesn't allow using column aliases (like total_tuition_fee) defined in the SELECT clause within the ON (or WHERE, GROUP BY) clause. This is because the alias is resolved after those clauses are executed—so the database doesn't recognize total_tuition_fee when evaluating the join condition.

Let's fix this with a few straightforward, practical approaches:

Approach 1: Direct Subquery for Max Tuition #

This method first calculates the highest total tuition across all courses, then joins the student and course tables to find students enrolled in those top courses:

SELECT 
    student.name, 
    course.description, 
    course.units * course.price_per_unit AS total_tuition_fee
FROM student
JOIN course ON student.course_id = course.id
WHERE course.units * course.price_per_unit = (
    SELECT MAX(units * price_per_unit) FROM course
);

Why this works: #

• The subquery computes the maximum total tuition first, so we have a concrete value to compare against.
• We use the actual calculation (units * price_per_unit) in the WHERE clause instead of the alias, since aliases aren't available at that stage of query execution.
• Using JOIN (instead of LEFT JOIN) makes sense here because we only care about students linked to courses with the highest tuition.

Approach 2: CTE for Improved Readability #

If you prefer cleaner, more maintainable code, a Common Table Expression (CTE) lets you precompute tuition totals for each course, then reuse that result in your main query:

WITH course_tuition AS (
    SELECT 
        id, 
        description, 
        units * price_per_unit AS total_tuition_fee
    FROM course
)
SELECT 
    student.name, 
    course_tuition.description, 
    course_tuition.total_tuition_fee
FROM student
JOIN course_tuition ON student.course_id = course_tuition.id
WHERE course_tuition.total_tuition_fee = (
    SELECT MAX(total_tuition_fee) FROM course_tuition
);

Why this works: #

• The CTE course_tuition calculates tuition totals once, so we can safely use the total_tuition_fee alias throughout the rest of the query.
• This structure makes the logic easier to follow, especially if you need to extend the query later (like adding filters or additional columns).

Approach 3: Handling Ties with Window Functions #

If multiple students are enrolled in courses with the same highest tuition, this method will return all of them using the RANK() window function:

SELECT 
    name, 
    description, 
    total_tuition_fee
FROM (
    SELECT 
        student.name, 
        course.description, 
        course.units * course.price_per_unit AS total_tuition_fee,
        RANK() OVER (ORDER BY course.units * course.price_per_unit DESC) AS tuition_rank
    FROM student
    JOIN course ON student.course_id = course.id
) ranked_students
WHERE tuition_rank = 1;

Why this works: #

• The inner query adds a tuition_rank column where the highest tuition gets a rank of 1.
• If multiple courses have the same max tuition, all students enrolled in those courses will have a rank of 1 and be included in the result.

内容的提问来源于stack exchange，提问作者Jan Ariel San Jose