mysql - 获取 SQL 错误 "every derived table must have its own alias sql"

Question

这个问题已经有答案了:

What is the error "Every derived table must have its own alias" in MySQL? (4 个回答)

已关闭 3 年前。

如何修复此查询 -

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
ORDER BY COUNT(Previous_Company_Id) DESC)));

<强> People Table 有四列

id, name, previous_company_name, current_company_name

<强> Company Table 有两列

id, name  

Answer 1

替换

    (SELECT COUNT(Previous_Company_Id) AS C 
FROM People GROUP BY Previous_Company_Id ORDER BY COUNT(Previous_Company_Id) DESC)

与

    (SELECT COUNT(Previous_Company_Id) AS C 
FROM People GROUP BY Previous_Company_Id ORDER BY COUNT(Previous_Company_Id) DESC) AA

您缺少别名。

您的新查询是:

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 ORDER BY COUNT(Previous_Company_Id) 
    DESC
)AA
));