sql – 自我加入以获取员工经理的姓名

Emp_id,Emp_Name and Mgr_id.

我正在尝试创建一个将列出的视图

Emp_id,Emp_name,Mgr_id和Mgr_name(通过交叉加入Employee表).我尝试了外连接,内连接等,但我无法做到正确.

任何帮助都非常感谢.

CREATE TABLE [dbo].[tblEmployeeDetails](
[emp_id] [bigint] NOT NULL,[emp_name] [nvarchar](200) NULL,[emp_mgr_id] [bigint] NULL,CONSTRAINT [PK_tblEmployeeDetails] PRIMARY KEY CLUSTERED (
[emp_id] ASC )WITH (PAD_INDEX  = OFF,STATISTICS_NORECOMPUTE  = OFF,IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS  = ON,ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]

解决方法

CREATE VIEW AS
SELECT e1.emp_Id EmployeeId,e1.emp_name EmployeeName,e1.emp_mgr_id ManagerId,e2.emp_name AS ManagerName
FROM   tblEmployeeDetails e1
       JOIN tblEmployeeDetails e2
       ON e1.emp_mgr_id = e2.emp_id

编辑：
左连接将起作用.

CREATE VIEW AS 
SELECT e1.emp_Id EmployeeId,e2.emp_name AS ManagerName 
FROM   tblEmployeeDetails e1 
       LEFT JOIN tblEmployeeDetails e2 
       ON e1.emp_mgr_id = e2.emp_id

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!