别名在查询中不起作用
问题描述:
我需要显示提出最大查询次数的用户及其部门的名称。我通过加入5个表格来编写以下查询; user
,query
,profile
,degree
和department
。别名在查询中不起作用
问题在于结果别名未被用于列名称。对于这两种列看起来像刚才name
select user.name 'USER_NAME',department.name 'DEPT_NAME'
from user
inner join query on (query.user_id=user.id)
inner join profile on (user.profile_id=profile.id)
inner join degree on (profile.degree_id=degree.id)
inner join department on (degree.department_id=department.id)
group by user.name
order by count(query.id) desc
limit 1
答
以下语法工作完美的我:
select U.name AS "USER_NAME", D.name AS "DEPT_NAME"
from user U
inner join query Q on (Q.user_id=U.id)
inner join profile P on (U.profile_id=P.id)
inner join degree C on (P.degree_id=C.id)
inner join department D on (C.department_id=D.id)
group by U.name
order by count(Q.id) desc
limit 1;
有时候,Mysql的喜欢你板着脸对你列一个别名,当您使用一些JOIN。
答
试试吧,
select thisuser.name 'USER_NAME',department.name 'DEPT_NAME'
from user as thisuser
inner join query on (query.user_id=thisuser.id)
inner join profile on (thisuser.profile_id=profile.id)
inner join degree on (profile.degree_id=degree.id)
inner join department on (degree.department_id=department.id)
group by thisuser.name
order by count(query.id) desc
limit 1
但是在构建查询时这不是一个好的做法; 它看起来像这样
select thisuser.name,thisdept.name from
(select name,user_id from user) as thisuser
inner join
(select name,user_id from department) as thisdept
on thisuser.user_id = thisdept.user_id
+0
仍然没有运气.. – PiGamma
答
只需删除从您的查询报价,它会工作,但你可以使用关键字AS
要清楚,你正在使用的别名。
我还要为了避免与关键字的混乱,同时也使用大写关键字使用小写的别名;
SELECT user.name AS user_name, department.name AS dept_name
...
别名应该没有单引号 –