如何仅使用一个表并仅使用1个id来修复管理器名称

Question

如何获得包含经理姓名列的结果集，如下所示:

+----+---------+-----------+
| id | empname |managername|
+----+---------+-----------+
|  1 | sandeep |   NULL    |        
|  2 | xyz     |   sandeep |
|  3 | abc     |   xyz     |         
+----+---------+-----------+

Answer 1

我认为你需要这样的查询:

SELECT t.id, t.empname, tt.id As managerid, tt.empname AS managername
FROM (
    SELECT t1.id, t1.empname, MAX(t2.id) AS nextId
    FROM yourTable t1
    LEFT JOIN yourTable t2 ON t1.id > t2.id
    GROUP BY t1.id, t1.empname) t
LEFT JOIN yourTable tt ON t.nextId = tt.id
ORDER BY t.id;

处理LAG()函数。

如果您使用SQL Server 2012+;您可以简单地使用:

SELECT id, empname, 
    LAG(id) OVER (ORDER BY id) As managerid, LAG(empname) OVER (ORDER BY id) As managername
FROM yourTable;

旧版本:

;WITH t AS (
    SELECT *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM yourTable) 
SELECT t.id, t.empname, tt.id, tt.empname
FROM t
LEFT JOIN t tt ON t.rn = tt.rn + 1;

Answer 2

try this query

select a.id , a.empname ,a.managerid,b.empname as managername 
from employee a
left outer join employee b on b.id=a.managerid