联合查询MSSQL中的三表联合查询实践

1. 联合查询MSSQL中的三表联合查询实践

在MSSQL数据库中,通过联合查询可以将两个或多个表中的数据合并,创建一个包含所有记录的结果集。在某些情况下,需要使用更多的表进行联合查询,以获取更复杂的结果。这里我们将探讨如何使用三个表进行联合查询。

1.1 创建三个表

首先,我们需要创建三个表,用于联合查询。为了简单起见,我们创建了三个表:部门(Department)、员工(Employee)和工资(Salary)。它们之间的关系是一对多,即一个部门有多个员工,一个员工只属于一个部门,一个员工对应着一个工资记录。

CREATE TABLE Department (

DepartmentID INT PRIMARY KEY NOT NULL,

DepartmentName VARCHAR(50) NOT NULL

);

CREATE TABLE Employee (

EmployeeID INT PRIMARY KEY NOT NULL,

EmployeeName VARCHAR(50) NOT NULL,

DepartmentID INT NOT NULL FOREIGN KEY REFERENCES Department(DepartmentID)

);

CREATE TABLE Salary (

SalaryID INT PRIMARY KEY NOT NULL,

EmployeeID INT NOT NULL FOREIGN KEY REFERENCES Employee(EmployeeID),

SalaryAmount NUMERIC(18, 2) NOT NULL

);

1.2 插入数据

接下来,我们需要插入一些数据来测试我们的联合查询。以下是插入的示例数据:

INSERT INTO Department (DepartmentID, DepartmentName) VALUES

(1, '技术部'),

(2, '财务部'),

(3, '销售部');

INSERT INTO Employee (EmployeeID, EmployeeName, DepartmentID) VALUES

(1, '张三', 1),

(2, '李四', 2),

(3, '王五', 3),

(4, '赵六', 1),

(5, '钱七', 2);

INSERT INTO Salary (SalaryID, EmployeeID, SalaryAmount) VALUES

(1, 1, 6000.00),

(2, 2, 8000.00),

(3, 3, 5000.00),

(4, 4, 7000.00),

(5, 5, 9000.00);

1.3 联合查询示例

现在,我们已经准备好进行联合查询。以下是一个示例查询,它返回每个部门的所有员工及其对应的工资。

SELECT d.DepartmentName, e.EmployeeName, s.SalaryAmount

FROM Department d

INNER JOIN Employee e ON d.DepartmentID = e.DepartmentID

INNER JOIN Salary s ON e.EmployeeID = s.EmployeeID;

上面的代码中,我们首先使用 INNER JOIN 关键字将部门表和员工表联接起来,然后再使用 INNER JOIN 关键字将员工表和工资表联接起来。由于每个员工只对应一个部门和一个工资记录,因此我们只需要使用 INNER JOIN 关键字即可。

2. 联合查询的类型

在MSSQL中,有三种类型的联合查询:UNION、UNION ALL和INTERSECT。它们分别用于合并两个或多个 SELECT 语句的结果集。

2.1 UNION

UNION 关键字用于合并两个或多个 SELECT 语句的结果集,并删除重复的行。

以下是 UNION 关键字的使用示例。它返回既在 Department 表中又在 Salary 表中出现过的所有部门名称和工资记录的总数:

SELECT DepartmentName FROM Department

UNION

SELECT CAST(COUNT(*) as VARCHAR) FROM Salary;

上面的代码中,我们首先查询 Department 表中的所有部门名称,然后使用 UNION 关键字将其与 Salary 表中的工资记录总数合并。由于 Salary 表中仅有一行记录,因此可以使用 COUNT 函数获取总数,并将其转换为字符串以与 DepartmentName 列的数据类型匹配。

2.2 UNION ALL

UNION ALL 关键字与 UNION 关键字的区别在于它不会删除重复的行。

以下是 UNION ALL 关键字的使用示例。它返回了所有部门名称以及每个部门对应的所有员工和工资记录:

SELECT d.DepartmentName, e.EmployeeName, s.SalaryAmount

FROM Department d

INNER JOIN Employee e ON d.DepartmentID = e.DepartmentID

INNER JOIN Salary s ON e.EmployeeID = s.EmployeeID

UNION ALL

SELECT DepartmentName, '', 0

FROM Department

WHERE DepartmentID NOT IN (SELECT DISTINCT DepartmentID FROM Employee);

上面的代码中,我们首先使用 INNER JOIN 关键字将部门表、员工表和工资表联接起来,然后使用 UNION ALL 关键字将其与另一个查询结果合并。这个查询结果获取了 Department 表中没有对应员工的部门名称,以及空字符串和零值作为占位符。我们使用 DISTINCT 关键字消除重复行。

2.3 INTERSECT

INTERSECT 关键字用于返回两个查询的交集。它仅返回同时出现在两个查询结果中的行,并且会自动删除重复行。

以下是 INTERSECT 关键字的使用示例。它返回既在 Department 表中又在 Employee 表中出现过的所有部门名称:

SELECT DepartmentName FROM Department

INTERSECT

SELECT DepartmentName FROM Employee;

上面的代码中,我们首先使用一条 SELECT 语句从 Department 表中获取所有部门名称,然后使用 INTERSECT 关键字将其与另一个 SELECT 语句的结果进行比较。这个 SELECT 语句从 Employee 表中获取所有已分配的部门名称。

3. 总结

在MSSQL中,三表联合查询是常见的数据查询技术之一。它可以帮助我们从多个表中获取更复杂的结果集,从而更好地满足业务需求。我们介绍了三种联合查询类型(UNION、UNION ALL和INTERSECT),并提供了使用示例。希望此文对您有所帮助。

免责声明:本文来自互联网,本站所有信息(包括但不限于文字、视频、音频、数据及图表),不保证该信息的准确性、真实性、完整性、有效性、及时性、原创性等,版权归属于原作者,如无意侵犯媒体或个人知识产权,请来电或致函告之,本站将在第一时间处理。猿码集站发布此文目的在于促进信息交流,此文观点与本站立场无关,不承担任何责任。

数据库标签