为什么在 Oracle 中，当我们从别名获取值时，我们无法将列指定为别名？

Question

对于前。 我的查询：

select * 
from 
    (SELECT distinct b.lname||' '||b.fname as "Manager",b.EMPID as "Mgrid" 
     from EMPLOYEE1) a, 
    (select lname,fname,empid from EMPLOYEE1) b 
where b.EMPID=a.MGRID) 

正在获取，

Manager           Mgrid    
----------------  -------- 
Farooque Umer     104      
Sontireddy Kiran  107      
Chopra Bhupendra  103  

但我无法写入：

select Manager, Mgrid 
from 
    (select distinct b.lname||' '||b.fname as "Manager",b.EMPID as "Mgrid" 
    from EMPLOYEE1) a, 
    (select lname,fname,empid 
    from EMPLOYEE1) b 
where b.EMPID=a.MGRID)

错误：

[错误] 脚本行：1-1 -------------------------- ORA-00904: “MGRID”: 无效标识符

Answer 1

因为一旦您开始将名称括在双引号（例如“Mgrid”）中，它们就会区分大小写。所以不要。这样做：

select Manager, Mgrid 
from 
    (select distinct b.lname||' '||b.fname as Manager,b.EMPID as Mgrid 
    from EMPLOYEE1) a, 
    (select lname,fname,empid 
    from EMPLOYEE1) b 
where b.EMPID=a.MGRID)

或者如果你真的喜欢区分大小写的名称，你必须这样做：

select "Manager", "Mgrid" 
from 
    (select distinct b.lname||' '||b.fname as "Manager",b.EMPID as "Mgrid" 
    from EMPLOYEE1) a, 
    (select lname,fname,empid 
    from EMPLOYEE1) b 
where b.EMPID=a."Mgrid")

Answer 2

您应该只在顶层选择应用修饰字段命名 - 所有其他标识符都应保留为有效的 Oracle 标识符。

select manager as "Manager", mgrid as "Mgrid" 
from 
    (select distinct b.lname||' '||b.fname as manager, b.EMPID as mgrid
    from EMPLOYEE1) a, 
    (select lname,fname,empid 
    from EMPLOYEE1) b 
where b.EMPID=a.MGRID)

Answer 3

除了引号之外，我认为您的帖子还多了一个括号：

select Manager, Mgrid 
from 
    (select distinct b.lname||' '||b.fname as Manager,b.EMPID as Mgrid 
    from EMPLOYEE1 a, 
    (select lname,fname,empid 
    from EMPLOYEE1) b 
    where b.empid =a.Mgrid)