主页 / 问题 / 1108896
Accepted
Bulson
Asked:2020-04-12 00:06:37 +0000 UTC2020-04-12 00:06:37 +0000 UTC

自引用或自加入关系如何提出请求？

  • 772

这是一张桌子

CREATE TABLE [dbo].[Employees] (
[Id]        INT           IDENTITY (1, 1) NOT NULL,
[Name]      NVARCHAR (50) NOT NULL,
[ManagerId] INT           NULL,
PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Employees_Employees] FOREIGN KEY ([ManagerId]) REFERENCES [dbo].[Employees] ([Id])
);

这样的要求

SELECT Id, Name, ManagerId
FROM dbo.Employees;

给出这个结果

+----+----------+-----------+
| Id |   Name   | ManagerId |
+----+----------+-----------+
|  1 | Иван     | NULL      |
|  2 | Мария    | 7         |
|  3 | Кристина | 7         |
|  4 | Виктор   | 1         |
|  5 | Сергей   | 1         |
|  6 | Григорий | 7         |
|  7 | Мирон    | 1         |
+----+----------+-----------+

如果提出这样的要求

SELECT em.Id
     , em.Name  AS 'Manager'
     , m.Name
FROM dbo.Employees AS em
RIGHT JOIN dbo.Employees AS m
    ON em.Id = m.ManagerId
ORDER BY em.Id;

变成这样

+------+---------+----------+
|  Id  | Manager |   Name   |
+------+---------+----------+
| NULL | NULL    | Иван     |
| 1    | Иван    | Виктор   |
| 1    | Иван    | Сергей   |
| 1    | Иван    | Мирон    |
| 7    | Мирон   | Григорий |
| 7    | Мирон   | Мария    |
| 7    | Мирон   | Кристина |
+------+---------+----------+

所以我试图转身LEFT JOIN

SELECT em.Id
     , em.Name  
     , m.Name AS 'Manager'
FROM dbo.Employees AS em
LEFT JOIN dbo.Employees AS m
    ON em.Id = m.ManagerId
ORDER BY m.Id;

我明白了

+----+----------+----------+
| Id |   Name   | Manager  |
+----+----------+----------+
|  2 | Мария    | NULL     |
|  3 | Кристина | NULL     |
|  4 | Виктор   | NULL     |
|  5 | Сергей   | NULL     |
|  6 | Григорий | NULL     |
|  7 | Мирон    | Мария    |
|  7 | Мирон    | Кристина |
|  1 | Иван     | Виктор   |
|  1 | Иван     | Сергей   |
|  7 | Мирон    | Григорий |
|  1 | Иван     | Мирон    |
+----+----------+----------+

如何修复并获取左侧的完整员工列表，右侧的经理是谁？

sql

1 个回答

  1. Best Answer

    如果您只需要获取有经理的员工列表，而不显示没有经理的员工，那么您需要提出以下请求

    SELECT em.Id
     , em.Name  
     , m.Name AS 'Manager'
FROM dbo.Employees AS em
JOIN dbo.Employees AS m
    ON em.Id = m.ManagerId
ORDER BY m.Id;

    如果您需要获取整个员工列表，甚至是那些没有经理的员工，那么您使用 LEFT JOIN 的查询就可以完成。

    如果您想获取所有经理，即使是那些没有员工的经理（右连接查询中右连接的替代方法），那么查询将如下所示：

    SELECT em.Id , em.Name , m.Name AS 'Manager' 
   FROM dbo.Employees AS m 
LEFT JOIN dbo.Employees AS em ON em.Id = m.ManagerId 
ORDER BY m.Id
    • 4

相关问题