从具有ID关系的当前表中获取数据 [英] Get data from current table with ID relation

问题描述

 hi guys 
 
我很困惑。 
 
如何获得经理名单？！ 
 
为什么我不能得到答案？

 EmployeeID名称经理
  1  john  NULL  
  2  sara  1  
  3  philip  1  
  4  tomas  3  
  5  celena  2  

我的尝试：

  SELECT 名称 FROM 员工 WHERE  EmployeeID = manager 

解决方案

如果您在经理姓名列表之后，那就不难了：

首先向返回的人写一个SELECT查询n只是经理的ID：

  SELECT   DISTINCT 经理 FROM 员工 WHERE 经理 IS   NOT   NULL  

返回：

< pre lang =text>经理
1
2
3

然后使用它来加入原始表格：

  SELECT  a。[名称]  FROM 员工a 
  JOIN （ SELECT   DISTINCT 经理 FROM 员工 WHERE 经理 IS   NOT   NULL ）b 
  ON  a.EmployeeID = b.Manager 

这会给你一个经理姓名列表：

姓名
 john 
 sara 
 philip 

如果您希望员工及其经理非常相似：

  SELECT  a。[名称]  AS  [经理名称]，b。名称 AS  [监督]  FROM 员工a 
  JOIN （ SELECT   DISTINCT 经理，姓名 FROM 员工 WHERE 经理 IS   NOT   NULL  ）b 
  ON  a.EmployeeID = b.Manager 

哪个会给你：

经理名称监督
 John philip 
 John sara 
 sara celena 
 philip tomas 

hi guys

I'm confused.

How can I get manager list?! 

Why can not I get an answer?

EmployeeID   name   manager
   1         john    NULL
   2         sara     1
   3         philip   1
   4         tomas    3
   5         celena   2

What I have tried:

SELECT Name FROM Employee WHERE EmployeeID = manager

解决方案

If you are after a list of manager names, then that's not too difficult:
First write a SELECT query to return just the IDs of Managers:

SELECT DISTINCT Manager FROM Employee WHERE Manager IS NOT NULL

That returns:

Manager
1
2
3

Then use that to JOIN to the original table:

SELECT a.[Name] FROM Employee a
JOIN (SELECT DISTINCT Manager FROM Employee WHERE Manager IS NOT NULL) b
ON a.EmployeeID = b.Manager

That gives you a list of manager names:

Name
john      
sara      
philip

If you want the employee and their manager it's very similar:

SELECT a.[Name] AS [Manager name], b.Name AS [Supervised] FROM Employee a
JOIN (SELECT DISTINCT Manager, Name FROM Employee WHERE Manager IS NOT NULL) b
ON a.EmployeeID = b.Manager

Which will give you:

Manager name	Supervised
John      	philip    
John      	sara      
sara      	celena    
philip    	tomas

这篇关于从具有ID关系的当前表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！