本文共 4363 字,大约阅读时间需要 14 分钟。
sql内联接外联接三张表
Here you will learn about joining three tables in sql with example.
在这里,您将通过示例了解有关在sql中联接三个表的信息。
Let us consider three tables Employee, Department and Project. We will see the complete example wherein these 3 tables are joined to produce a result:
让我们考虑雇员,部门和项目三个表。 我们将看到完整的示例,其中将这3个表连接起来以产生结果:
This table records the Id of the Employee which is the primary key, the name of the working Employee, the Department Id of the Department they are working in and the Project they are working for.
此表记录了作为主键的Employee ID,工作Employee的名称,他们正在工作的部门的部门ID以及他们正在工作的项目。
Table creation:
表创建:
CREATE TABLE Employee(EId int primary key, EName varchar(20), DId int, PId int) INSERT INTO Employee VALUES(1, Ramesh) INSERT INTO Employee VALUES(2, Somesh) INSERT INTO Employee VALUES(3, Rajesh) INSERT INTO Employee VALUES(4, Ram) INSERT INTO Employee VALUES(5, Ishi) INSERT INTO Employee VALUES(6, Rekha) INSERT INTO Employee VALUES(7,Mukesh)
This table looks like:
该表如下所示:
EId | EName | DId | PId |
1 | Ramesh | 3 | 1 |
2 | Somesh | 2 | 3 |
3 | Rajesh | 1 | 5 |
4 | Ram | 3 | 4 |
5 | Ishi | 2 | 3 |
6 | Rekha | 4 | 2 |
7 | Mukesh | 1 | 5 |
Id | 姓名 | 直拨 | 标牌 |
1个 | 拉梅什 | 3 | 1个 |
2 | Somesh | 2 | 3 |
3 | 拉杰什 | 1个 | 5 |
4 | 内存 | 3 | 4 |
5 | 石井 | 2 | 3 |
6 | 雷卡 | 4 | 2 |
7 | 穆克什 | 1个 | 5 |
This table contains the Department an Employee Its attributes are:
该表包含部门和雇员,其属性为:
Table creation:
表创建:
CREATE TABLE Department(DId int primary key, DName varchar(20)) INSERT INTO Department VALUES(1, Manufacturing) INSERT INTO Department VALUES(2, HR) INSERT INTO Department VALUES(3, RnD) INSERT INTO Department VALUES(4, Accounting) INSERT INTO Department VALUES(5, IT)
DId | DName |
1 | Manufacturing |
2 | HR |
3 | RnD |
4 | Accounting |
5 | IT |
直拨 | 名称 |
1个 | 制造业 |
2 | 人力资源 |
3 | n |
4 | 会计 |
5 | 它 |
Holds the Project name an Employee is dealing in. It contains the following attributes:
拥有一个雇员正在处理的项目名称。它包含以下属性:
Table Creation:
表创建:
CREATE TABLE Project(PId int primary key, PName varchar(20) ) INSERT INTO Project VALUES(1, Machine Learning) INSERT INTO Project VALUES(2, Taxes) INSERT INTO Project VALUES(3, AskHR Portal) INSERT INTO Project VALUES(4, Blockchain) INSERT INTO Project VALUES(5, CAD) INSERT INTO Project VALUES(6, PR)
PId | PName |
1 | Machine Learning |
2 | Taxes |
3 | AskHR Portal |
4 | Blockchain |
5 | CAD |
6 | PR |
标牌 | 名称 |
1个 | 机器学习 |
2 | 税收 |
3 | AskHR门户 |
4 | 区块链 |
5 | 电脑辅助设计 |
6 | 公关 |
Now, we have already created the three tables we need to work on. Let us consider a case where we need to display the Employee Name, the Department Name and the Project Name together, we would require to join the three tables:
现在,我们已经创建了需要处理的三个表。 让我们考虑一下需要同时显示“雇员姓名”,“部门名称”和“项目名称”的情况,我们需要将三个表连接起来:
If, we have more than three tables with us, we can simply extend the same procedure to multiple tables, i.e, take the resultant of (n-1) tables and join with the nth table.
如果我们拥有三个以上的表,则可以简单地将同一过程扩展到多个表,即,取(n-1)个表的结果并与第n个表联接。
As far as this example is concerned, we will have the QUERY as:
就此示例而言,我们将QUERY作为:
SELECT Employee.EId, Employee.EName, Department.DName, Project.PName FROM ( (Employee INNER JOIN Department ON Employee.DId = Department.DId) INNER JOIN Project ON Employee.PId = Project.PId);
The resultant table would be:
结果表将是:
EId | EName | DName | PName |
1 | Ramesh | RnD | Machine Learning |
2 | Somesh | HR | AskHR Portal |
3 | Rajesh | Manufacturing | CAD |
4 | Ram | Rnd | Blockchain |
5 | Ishi | HR | AskHR Portal |
6 | Rekha | Accounting | Taxes |
7 | Mukesh | Manufacturing | CAD |
Id | 姓名 | 名称 | 名称 |
1个 | 拉梅什 | n | 机器学习 |
2 | Somesh | 人力资源 | AskHR门户 |
3 | 拉杰什 | 制造业 | 电脑辅助设计 |
4 | 内存 | Rnd | 区块链 |
5 | 石井 | 人力资源 | AskHR门户 |
6 | 雷卡 | 会计 | 税收 |
7 | 穆克什 | 制造业 | 电脑辅助设计 |
Hence, we can conclude that the joining of the table is as easy as it is important. Once we get familiar with joining two tables, we won’t have to worry about joining multiple tables.
因此,我们可以得出结论,表的连接既简单又重要。 一旦我们熟悉了联接两个表,就不必担心联接多个表。
翻译自:
sql内联接外联接三张表
转载地址:http://ivggb.baihongyu.com/