你好，我有一个EMPLOYEE表有以下几列
Emp_id, Emp_Name and Mgr_id.
我想创建一个视图，将列出
Emp_id, Emp_name, Mgr_id and 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]
Answer 1:
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
编辑 ：左连接会工作，如果emp_mgr_id为null。
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
Answer 2:
SELECT b.Emp_id, b.Emp_name,e.emp_id as managerID, e.emp_name as managerName
FROM Employee b
JOIN Employee e ON b.Emp_ID = e.emp_mgr_id
试试这个，这是一个对自己JOIN得到管理者:)
Answer 3:
CREATE VIEW EmployeeWithManager AS
SELECT e.[emp id], e.[emp name], m.[emp id], m.[emp name]
FROM Employee e LEFT JOIN Employee m ON e.[emp mgr id] = m.[emp id]
这个定义使用左外连接，这意味着即使员工其经理ID为NULL，或者其经理已被删除（如果您的应用程序允许）将上市，与他们的经理的属性返回NULL。
如果你使用一个内部联接相反，只有谁拥有的经理人会被列出。
Answer 4:
select E1.emp_id [Emp_id],E1.emp_name [Emp_name],
E2.emp_mgr_id [Mgr_id],E2.emp_name [Mgr_name]
from [tblEmployeeDetails] E1 left outer join
[tblEmployeeDetails] E2
on E1.emp_mgr_id=E2.emp_id
Answer 5:
试试这个。
SELECT Employee.emp_id, Employee.emp_name,Manager.emp_id as Mgr_Id, Manager.emp_name as Mgr_Name
FROM tblEmployeeDetails Employee
LEFT JOIN tblEmployeeDetails Manager ON Employee.emp_mgr_id = Manager.emp_id
Answer 6:
杰西说，使用自联接：
SELECT
e.emp_id
, e.emp_name
, e.emp_mgr_id
, m.emp_name AS mgr_name
FROM [dbo].[tblEmployeeDetails] e
LEFT JOIN [dbo].[tblEmployeeDetails] m ON e.emp_mgr_id = m.emp_id
Answer 7:
SELECT e1.empno EmployeeId, e1.ename EmployeeName,
e1.mgr ManagerId, e2.ename AS ManagerName
FROM emp e1, emp e2
where e1.mgr = e2.empno
Answer 8:
试试这个..你应该做的LEFT JOIN表中以igore空值
SELECT a.emp_Id EmployeeId, a.emp_name EmployeeName,
a.emp_mgr_id ManagerId, b.emp_name AS ManagerName
FROM tblEmployeeDetails a
LEFT JOIN tblEmployeeDetails b
ON b.emp_mgr_id = b.emp_id
Answer 9:
TableName :Manager
EmpId EmpName ManagerId
1 Monib 4
2 zahir 1
3 Sarfudding NULL
4 Aslam 3
select e.EmpId as EmployeeId,e.EmpName as EmployeeName,e.ManagerId as ManagerId,e1.EmpName as Managername from Manager e
join manager e1 on e.ManagerId=e1.empId
Answer 10:
create table abc(emp_ID int, manager varchar(20) , manager_id int)
emp_ID manager manager_id
1 abc NULL
2 def 1
3 ghi 2
4 klm 3
5 def1 1
6 ghi1 2
7 klm1 3
select a.emp_ID , a.manager emp_name,b.manager manager_name
from abc a
left join abc b
on a.manager_id = b.emp_ID
Result:
emp_ID emp_name manager_name
1 abc NULL
2 def abc
3 ghi def
4 klm ghi
5 def1 abc
6 ghi1 def
7 klm1 ghi
Answer 11:
create view as
(select
e1.empno as PersonID,
e1.ename as PersonName,
e2.empno MANAGER_ID,
e2.ename MANAGER_NAME
from
employees e1 , employees e2
where
e2.empno=e1.mgr)
Answer 12:
select E1.EmpId,E1.Name,E2.Name as Manager from Employee E1 left join Employee E2 on E1.ManagerID = E2.EmpId
Answer 13:
此外，您可能希望得到经理和他们的报告与统计 -
SELECT e2.ename ,count(e1.ename) FROM employee_s e1 LEFT OUTER JOIN employee_s e2
ON e1.manager_id = e2.eid
group by e2.ename;
Answer 14:
SELECT e1.emp_id, e1.emp_name, e1.mgr_id, e2.emp_name as manager_name
FROM employee e1
JOIN employee e2
ON e1.mgr_id = e2.emp_id
ORDER BY e1.emp_id
*这里是链接到SQL拨弄工作的例子。 http://www.sqlfiddle.com/#!17/392b5/9
文章来源: Self Join to get employee manager name
