所以我在代码中使用了'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);上面写着:桌子'company.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);它正常工作。有什么建议吗?
发布于 2016-11-04 16:42:55
为子查询分配别名并不意味着它可以完全像表一样处理。
在第一个查询中,mysql无法知道select max(NUMBER_OF_ORDERS) from O中的select max(NUMBER_OF_ORDERS) from O不是一个表。这就是它对待它的方式,因此出现了错误。
company是您模式的名称吗?
顺便说一句,也许您可以使用以下方法返回相同的信息:
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;https://stackoverflow.com/questions/40427530
复制相似问题