SQL错误‘every derived table must have its own alias’排查及查询修复咨询

Got it, let's tackle this error head-on. The root cause is exactly what the message states: any subquery that acts as a derived table (a temporary table created by the query) needs a unique alias so the database can reference it. In your query, the innermost subquery that calculates counts of Previous_Company_Id is being used as a source for the MAX(C) calculation, but it doesn't have an alias attached — that's what's triggering the error.

Fixed Query #

Here's the corrected version with the necessary alias added:

SELECT People.Name, Company.Name 
FROM People 
JOIN Company ON People.Current_Company_ID = Company.ID 
WHERE Current_Company_ID IN (
    SELECT Previous_Company_ID 
    FROM People 
    GROUP BY Previous_Company_ID 
    HAVING Count(Previous_Company_ID) = (
        SELECT MAX(C) 
        FROM (
            SELECT COUNT(Previous_Company_Id) AS C 
            FROM People 
            GROUP BY Previous_Company_Id
        ) AS company_counts -- This alias fixes the derived table error
    )
);

What Changed? #

• I added AS company_counts to the innermost subquery. You can use any valid alias name here (like count_sub or temp_counts) — the key is just giving the derived table a name the outer query can recognize.

Quick Note on Column Names #

You mentioned the People table has columns previous_company_name and current_company_name, but your query uses Previous_Company_ID and Current_Company_ID. If this is a typo:

• Adjust the JOIN condition to match your actual column names (e.g., People.current_company_name = Company.Name)
• Update the subqueries to use previous_company_name instead of Previous_Company_ID

If the ID columns do exist in your table, then you're good to go with the fixed query above.

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