各部门负责人姓名
各部门负责人姓名
提问于 2020-03-08 21:05:40
我的数据在雇员表中，如下所示。
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))提前感谢您的帮助！
回答 3
Stack Overflow用户
回答已采纳
发布于 2020-03-08 21: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.EIdStack Overflow用户
发布于 2020-03-08 21: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.ManagerStack Overflow用户
发布于 2020-03-09 18: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页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60587739
复制相关文章
相似问题
腾讯云开发者
