我正在学习SQL,遇到了一个需要self 联接的问题.我一直在try 这个查询,以理解为什么自连接表以这种方式布局它,但一直没有太多的运气
Create table Departments (DepartmentID int primary key, Name text);
insert into Departments values
(1001,'SRO'),
(2001,'Drs'),
(3001,'Accounting');
Create table Employees
(
EmployeeID int not null,
DepartmentID int not null,
ManagerID int,
Name varchar(50) not null,
Salary int not null,
primary key(EmployeeID),
foreign key (DepartmentID)
references Departments(DepartmentID)
);
insert into Employees values
(68319,1001,NULL,'Kayling','6000.00'),
(66928,3001,68319,'Blaze','2750.00'),
(67832,1001,68319,'Clare','2550.00'),
(65646,2001,68319,'Jonas','2957.00'),
(67858,2001,65646,'Scarlet','3100.00'),
(69062,2001,65646,'Frank','3100.00'),
(63679,2001,69062,'Sandrine','900.00'),
(64989,3001,66928,'Adelyn','1700.00'),
(65271,3001,66928,'Wade','1350.00');
现在执行两个查询,以了解该表最初是什么样子,以及在自连接之后是什么样子
查询1
select * from Employees order by Employeeid;
问题2
select * from Employees e1
join Employees e2 on
e2.ManagerID = e1.EmployeeID
order by e1.employeeid;
我得到的结果是:
查询1
EmployeeID DepartmentID ManagerID Name Salary
63679 2001 69062 Sandrine 900
64989 3001 66928 Adelyn 1700
65271 3001 66928 Wade 1350
65646 2001 68319 Jonas 2957
66928 3001 68319 Blaze 2750
67832 1001 68319 Clare 2550
67858 2001 65646 Scarlet 3100
68319 1001 null Kayling 6000
69062 2001 65646 Frank 3100
问题2 Results
EmployeeID DepartmentID ManagerID Name Salary EmployeeID DepartmentID ManagerID Name Salary
65646 2001 68319 Jonas 2957 69062 2001 65646 Frank 3100
65646 2001 68319 Jonas 2957 67858 2001 65646 Scarlet 3100
66928 3001 68319 Blaze 2750 65271 3001 66928 Wade 1350
66928 3001 68319 Blaze 2750 64989 3001 66928 Adelyn 1700
68319 1001 null Kayling 6000 67832 1001 68319 Clare 2550
68319 1001 null Kayling 6000 66928 3001 68319 Blaze 2750
68319 1001 null Kayling 6000 65646 2001 68319 Jonas 2957
69062 2001 65646 Frank 3100 63679 2001 69062 Sandrine 900
现在,我对查询2的理解是,在第一列中,只显示管理员ID,而不显示其他任何人(例如,仅显示员工).
这种类型的self 联接应该允许我找到由每个经理管理的所有员工(职员).新的连接表如何记住第一个工资列与经理相关,而最后一个列与员工相关.表中没有智能,而是查询中有智能,例如e1和e1?