基于员工ID管理的SQL。

Question

你好，我必须写一个查询，在这里我必须显示：

• 经理号码
• 经理的全名(第一列、第二列和最后一列的组合)
• 雇员人数
• 员工全名(同样是第一名、第一名和最后一名)

他们都必须是为一个特定的经理，戴夫。戴夫的雇员身份证是100，如果这有帮助的话。

到目前为止我已经

SELECT 
    EMP_MGR as "Manager's Number", 
    EMP_NUM as "Employee's Number", 
    EMP_FNAME || ' ' || EMP_INITIAL || '. ' || EMP_LNAME AS "Employee's Full Name", 

FROM EMP
    WHERE EMP_MGR = 108

我知道我必须在某个地方使用一个连接，但我对如何做到这一点很困惑。

Answer 1

你能做到的

select
    M.EMP_NUM as "Manager's Number", 
    M.EMP_FNAME + ' ' + M.EMP_INITIAL + '. ' + M.EMP_LNAME AS "Manager's Full Name", 
    E.EMP_NUM as "Employee's Number", 
    E.EMP_FNAME + ' ' + E.EMP_INITIAL + '. ' + E.EMP_LNAME AS "Employee's Full Name"
from EMP as M
    inner join EMP as E on E.EMP_MGR = M.EMP_NUM 
where M.EMP_NUM = 100

Answer 2

SELECT 
    A.EMP_MGR as "Manager's Number", 
    B.EMP_NUM as "Employee's Number", 
    A.EMP_FNAME || ' ' || A.EMP_INITIAL || '. ' || A.EMP_LNAME AS "Manager's Full Name",
    B.EMP_FNAME || ' ' || B.EMP_INITIAL || '. ' || B.EMP_LNAME AS "Employee's Full Name", 

FROM EMP A
INNER JOIN EMP B ON A.EMP_NUM = B.EMP_MGR AND A.EMP_NUM = 108

Answer 3

试试这个：

select
    M.EMP_NUM as "Manager's Number", 
    M.EMP_FNAME + ' ' + M.EMP_INITIAL + '. ' + M.EMP_LNAME AS "Manager's Full Name", 
    E.EMP_NUM as "Employee's Number", 
    E.EMP_FNAME + ' ' + E.EMP_INITIAL + '. ' + E.EMP_LNAME AS "Employee's Full Name"
from EMP as M
    inner join EMP as E on E.EMP_MGR = M.EMP_NUM 
where M.EMP_NUM = '100'

唯一的区别是我把100放在单引号里。我的猜测是，名为"EMP_NUM“的字段存储为字符串，而不是数字。如果是这样，请向数据库设计人员抱怨选择一个非常糟糕的名称。帮不上忙。。。但他们可能知道。