自我加入以获得员工经理姓名 [英] Self Join to get employee manager name

问题描述

您好，我有一个Employee表，其中包含以下列：

Hello I have a Employee Table with following columns

Emp_id，Emp_Name和Mgr_id。

Emp_id, Emp_Name and Mgr_id.

尝试创建一个视图，其中将列出

I am trying to create a view which will list

Emp_id，Emp_name，Mgr_id和Mgr_name（通过交叉加入Employee表）。我尝试外连接，内连接等，但我不能得到它的权利。

Emp_id, Emp_name, Mgr_id and Mgr_name (by cross joining the Employee table). I tried outer join, inner join etc, but I am not able to get it right.

任何帮助都非常感激。

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

EDIT ：
Left Join将工作。

EDIT: Left Join will work.

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 
       LEFT JOIN tblEmployeeDetails e2 
       ON e1.emp_mgr_id = e2.emp_id

这篇关于自我加入以获得员工经理姓名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！