首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >MySQL表别名不起作用

问MySQL表别名不起作用
EN

Stack Overflow用户
提问于 2016-11-04 16:27:53
回答 1查看 1.3K关注 0票数 1

所以我在代码中使用了'as‘命令。

代码语言:javascript
复制
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‘不存在。但当我这样做的时候

代码语言:javascript
复制
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);

它正常工作。有什么建议吗?

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2016-11-04 16:42:55

为子查询分配别名并不意味着它可以完全像表一样处理。

在第一个查询中,mysql无法知道select max(NUMBER_OF_ORDERS) from O中的select max(NUMBER_OF_ORDERS) from O不是一个表。这就是它对待它的方式,因此出现了错误。

company是您模式的名称吗?

顺便说一句,也许您可以使用以下方法返回相同的信息:

代码语言:javascript
复制
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;
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/40427530

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档