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),并提供了使用示例。希望此文对您有所帮助。