需要替代加入sql server中的同一个表

I want to fetch Dept name from the below table for every row. [image 1]

我想从下表中获取每行的Dept名称。 [图片1]

Employee | Manager | Dept
---------+---------+-------------
Santosh  | Gyan    |
Rahul    |         | HR
Suresh   | Gyan    |
Gyan     |         | Technology

Every manager is also an Employee.
每位经理都是员工。
IF Dept name is not available for an Employee, then I have to search his manager in Employee column and find his Dept.
如果部门名称不适用于员工，那么我必须在员工专栏中搜索他的经理并找到他的部门。

Attached is the expected output. [image 2]

附上的是预期产量。 [图片2]

Employee | Manager | Dept
---------+---------+------------
Santosh  | Gyan    | Technology
Rahul    |         | HR
Suresh   | Gyan    | Technology
Gyan     |         | Technology

Have tried Right joining the same table, it gives the result but takes too much of time on a large table. So please suggest an alternative way.

尝试过右连接同一个表，它会给出结果但在大表上花费太多时间。所以请提出另一种方法。

5 个解决方案

#1

You can use a query like:

您可以使用如下查询：

SELECT 
    Employee, 
    Manager,
    CASE WHEN Dept = '' THEN (
        SELECT t.Dept 
        FROM yourTable t 
        WHERE t.Employee = yourTable.Manager
    )
    ELSE Dept
    END As Dept
FROM 
    yourTable;

[SQL Fiddle Demo]

[SQL小提琴演示]

This query is just as a SQL translation of what you say ;).

这个查询就像你说的那样的SQL翻译;）。

#2

To gain the Query performance u could use Union All instead.

要获得查询性能，您可以使用Union All。

    SELECT EMPLOYEE, DEPT FROM EMPLOYEE WHERE DEPT IS NOT NULL
    UNION ALL
    SELECT E.EMPLOYEE, EM.DEPT FROM EMPLOYEE E
    INNER JOIN EMPLOYEE EM ON E.MANAGER = EM.MANAGER
    AND E.DEPT IS  NULL

#3

Try This :

尝试这个：

SELECT e.Employee, e.Manager, COALESCE(e.Dept, e1.Dept) Dept
FROM Employees e 
LEFT JOIN Employees e1 ON e1.Employee = e.Manager

OR:

要么：

SELECT e.Employee, e.Manager, COALESCE(e.Dept, t.Dept) Dept
FROM Employees e 
OUTER APPLY (SELECT dept FROM Employees e1 WHERE e1.employee = e.manager) t

#4

Perhaps a left join on a distinct list

也许左边的联接在一个不同的列表上

Declare @YourTable table (Employee varchar(50),Manager varchar(50),Dept varchar(50))
Insert Into @YourTable values
('Santosh','Gyan',''),
('Rahul','','HR'),
('Suresh','Gyan',''),
('Gyan','','Technology')

Select A.Employee
      ,A.Manager
      ,Dept = Isnull(B.Dept,A.Dept)
 From  @YourTable A
 Left Join (Select Distinct Employee,Dept from @YourTable Where Dept<>'') B
   on  A.Manager=B.Employee

Or even

甚至

Select A.Employee
      ,A.Manager
      ,Dept = case when A.Dept='' then B.Dept else A.Dept end
 From  @YourTable A
 Outer Apply (Select Dept from @YourTable Where Employee=A.Manager) B

Returns

Employee    Manager Dept
Santosh     Gyan    Technology
Rahul               HR
Suresh      Gyan    Technology
Gyan                Technology

#5

Try this,

尝试这个，

SELECT e.Employee, e.Manager, ISNULL(e.Dept, em.Dept)
FROM Employees e 
    LEFT JOIN Employees em ON em.Employee = e.Manager
        AND e.Dept = ''

#1