英文:
Filter column with other column in the same table
问题
我正在学习SQL，我想使用ReportsTo的值来获取EmployeeId，然后使用它来获取FullName，而不使用JOIN。最终的结果应该显示谁有多少下属。
| EmployeeID | FullName | ReportsTo |
|---|---|---|
| 1 | Nancy Davolio | 2 |
| 2 | Andrew Fuller | NULL |
| 3 | Janet Leverling | 2 |
| 4 | Margaret Peacock | 2 |
| 5 | Steven Buchanan | 2 |
| 6 | Michael Suyama | 5 |
| 7 | Robert King | 5 |
| 8 | Laura Callahan | 2 |
| 9 | Anne Dodsworth | 5 |
Select
(Select FirstName + ' ' + LastName
From Employees
Where EmployeeID = ReportsTo) as EmployeeName,
Count(EmployeeID) as SubordinateAmount,
ReportsTo
From
Employees
Where
ReportsTo is not Null
Group By
ReportsTo
结果应该如下：
| FullName | Subordinates |
|---|---|
| Andrew Fuller | 5 |
| Steven Buchanan | 3 |
英文:
I am learning SQL and I want to use ReportsTo value to get the EmployeeId and use it to get FullName without using JOIN. The final results should show who has how many subordinates.
| EmployeeID | FullName | ReportsTo |
|---|---|---|
| 1 | Nancy Davolio | 2 |
| 2 | Andrew Fuller | NULL |
| 3 | Janet Leverling | 2 |
| 4 | Margaret Peacock | 2 |
| 5 | Steven Buchanan | 2 |
| 6 | Michael Suyama | 5 |
| 7 | Robert King | 5 |
| 8 | Laura Callahan | 2 |
| 9 | Anne Dodsworth | 5 |
Select
(Select FirstName + ' ' + LastName
From Employees
Where EmployeeID = ReportsTo) as EmployeeName,
Count(EmployeeID) as SubordinateAmount,
ReportsTo
From
Employees
Where
ReportsTo is not Null
Group By
ReportsTo
The result should be like:
| FullName | Subordinates |
|---|---|
| Andrew Fuller | 5 |
| Steven Buchanan | 3 |
I have tried using a subquery by EmployeeID = ReportsTo, but it returns Null, and I can't figure out what to use.
答案1
得分: 0
你可以像这样做：
select *
into #data
from (
VALUES (1, N'Nancy Davolio', N'2')
, (2, N'Andrew Fuller', NULL)
, (3, N'Janet Leverling', N'2')
, (4, N'Margaret Peacock', N'2')
, (5, N'Steven Buchanan', N'2')
, (6, N'Michael Suyama', N'5')
, (7, N'Robert King', N'5')
, (8, N'Laura Callahan', N'2')
, (9, N'Anne Dodsworth', N'5')
) t (EmployeeID,FullName,ReportsTo)
select COUNT(*)
, (select fullname from #data d2 where d2.EmployeeID = d.reportsto)
from #data d
where ReportsTo is not null
group by reportsto
它通过相关子查询获取必要的数据。但我不太确定为什么不愿意使用连接，通常使用连接更好而且更清晰。
英文:
You can do something like this i guess:
select *
into #data
from (
VALUES (1, N'Nancy Davolio', N'2')
, (2, N'Andrew Fuller', NULL)
, (3, N'Janet Leverling', N'2')
, (4, N'Margaret Peacock', N'2')
, (5, N'Steven Buchanan', N'2')
, (6, N'Michael Suyama', N'5')
, (7, N'Robert King', N'5')
, (8, N'Laura Callahan', N'2')
, (9, N'Anne Dodsworth', N'5')
) t (EmployeeID,FullName,ReportsTo)
select COUNT(*)
, (select fullname from #data d2 where d2.EmployeeID = d.reportsto)
from #data d
where ReportsTo is not null
group by reportsto
It fetches the necessary data by correlated subquery. But i'm not sure why the reluctance to use a join, it's "usually" better and more clearer to use
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论