问各部门负责人姓名
EN

Stack Overflow用户

提问于 2020-03-08 13:05:40

回答 3查看 54关注 0票数 0

我的数据在雇员表中，如下所示。

EId FName LName  Sal  Manager Department
1   sham    rao 5000    2   sales
2   Raju    rao 6000    4   Insurance
3   Shila   rao 4000    4   Insurance
5   test    rao 500     7   Sports
6   sham    rao 5000    2   sales
7   Jane    rao 5000    0   Head
4   Rajesh  rao 600     7   Sports

我必须找到部门负责人的名字，就像下面这样。

ManagerName            Manager      department
Rajurao                2            sales
Rajeshrao              4            Insurance
Janerao                7            Sports
Null                  Null          Head

我尝试了下面的查询来找出它，但是没有成功

select concat (first_name,'',last_name) as name,department from employee 
where   exists (select distinct manager from employee where  exists (select department, 
count(manager) from employee group by department))

提前感谢您的帮助！

sql

sql-server

回答 3

Stack Overflow用户

回答已采纳

发布于 2020-03-08 13:24:53

SELECT DISTINCT ManName.FName + ManName.LName AS ManagerName, CASE WHEN Dep.Manager = 0 THEN Null ELSE Dep.Manager END AS Manager, Dep.Department
FROM Employee Dep
LEFT JOIN Employee ManName ON Dep.Manager = ManName.EId

票数 0

Stack Overflow用户

发布于 2020-03-08 13:14:40

将同一个表连接到自身将会起到这个作用：

SELECT CONCAT(first_name,'',last_name) AS emplself.Name, emplself.Department 
FROM employee AS empl
INNER JOIN employee AS emplself ON empl.EId=emplself.Manager

票数 1

Stack Overflow用户

发布于 2020-03-09 10:24:20

使用递归Cte尝试下面的代码

;WITH DataCTE (EId, FName, LName,  Sal , Manager, Department)
AS
(
SELECT 1,'sham'    ,'rao', 5000 , 2 ,'sales'
UNION ALL SELECT 2,'Raju'    ,'rao', 6000 , 4 ,'Insurance'
UNION ALL SELECT 3,'Shila'   ,'rao', 4000 , 4 ,'Insurance'
UNION ALL SELECT 5,'Test'    ,'rao', 500  , 7 ,'Sports'
UNION ALL SELECT 6,'sham'    ,'rao', 5000 , 2 ,'sales'
UNION ALL SELECT 7,'Jane'    ,'rao', 5000 , 0 ,'Head'
UNION ALL SELECT 4,'Rajesh'  ,'rao', 600  , 7 ,'Sports'
)
,CTE_Hierarchy
AS

(
SELECT EId, 
       CONCAT(FName,' ',LName) AS EmpName ,
       Manager AS ManagerId,
       Department
 FROM  DataCTE
 WHERE Manager = 0
 UNION ALL

 SELECT e.EId, 
        CONCAT(e.FName,' ',e.LName) ,
        e.Manager,
        e.Department
 FROM CTE_Hierarchy c
 INNER JOIN DataCTE e
 ON c.EId = e.Manager

 )
 SELECT  DISTINCT EmpName, 
 ManagerId,
        ISNULL((SELECT  CONCAT(d.FName,' ',d.LName)
         FROM DataCTE d
         WHERE d.EId = CTE_Hierarchy.ManagerId),'')  AS ManagerName,
 Department
 FROM CTE_Hierarchy
 ORDER BY 2

结果

EmpName     ManagerId   ManagerName     Department
----------------------------------------------------
Jane rao        0                        Head
sham rao        2         Raju rao       sales
Raju rao        4         Rajesh rao     Insurance
Shila rao       4         Rajesh rao     Insurance
Rajesh rao      7         Jane rao       Sports
Test rao        7         Jane rao       Sports