英文:
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 + ' ' + LastNameFrom EmployeesWhere EmployeeID = ReportsTo) as EmployeeName,Count(EmployeeID) as SubordinateAmount,ReportsToFromEmployeesWhereReportsTo is not NullGroup ByReportsTo
结果应该如下：
| 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 + ' ' + LastNameFrom EmployeesWhere EmployeeID = ReportsTo) as EmployeeName,Count(EmployeeID) as SubordinateAmount,ReportsToFromEmployeesWhereReportsTo is not NullGroup ByReportsTo
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 #datafrom (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 dwhere ReportsTo is not nullgroup by reportsto
它通过相关子查询获取必要的数据。但我不太确定为什么不愿意使用连接，通常使用连接更好而且更清晰。
英文:
You can do something like this i guess:
select *into #datafrom (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 dwhere ReportsTo is not nullgroup 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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论