所以我在代码中使用'as'命令
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,NUMBER_OF_ORDERS
from(
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,count(CUSTOMER_ID) as NUMBER_OF_ORDERS
from customer as C
right join
customer_hotel as CH on CH.CUSTOMER_ID = C.ID
group by(CH.CUSTOMER_ID)
) as O
where NUMBER_OF_ORDERS = (select max(NUMBER_OF_ORDERS) from O);
它说：桌子“公司”不存在。
但当我这样做的时候
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,NUMBER_OF_ORDERS
from(
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,count(CUSTOMER_ID) as NUMBER_OF_ORDERS
from customer as C
right join
customer_hotel as CH on CH.CUSTOMER_ID = C.ID
group by(CH.CUSTOMER_ID)
) as O
where NUMBER_OF_ORDERS = (select max(NUMBER_OF_ORDERS)
from(
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,count(CUSTOMER_ID) as NUMBER_OF_ORDERS
from customer as C
right join
customer_hotel as CH on CH.CUSTOMER_ID = C.ID
group by(CH.CUSTOMER_ID)
) as O);
它工作正常。有什么建议吗？
最佳答案
为子查询分配别名并不意味着可以将其完全视为表。
在第一个查询中，mysql无法知道O中的select max(NUMBER_OF_ORDERS) from O不是一个表。所以这就是它对待它的方式，因此产生了错误。
是您的模式名吗？
顺便说一句，也许您可以使用以下方法返回相同的信息：
select PERSONAL_ID,NAME,SURNAME,BIRTH_DATE,count(CUSTOMER_ID) as NUMBER_OF_ORDERS
from customer as C
right join
customer_hotel as CH on CH.CUSTOMER_ID = C.ID
group by(CH.CUSTOMER_ID)
order by count(CUSTOMER_ID) desc
limit 1;
关于mysql - MySQL表别名不起作用，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40427530/