多表树结构查询与构建
介绍
MSSQL是一种关系型数据库管理系统，它可以存储和管理表格型数据。在一些应用开发中，我们需要将多个表格构建成树形结构，这种多表树形结构数据的查询和构建是一项非常重要的工作。在这篇文章中，我们将介绍如何使用MSSQL实现多表树结构查询与构建。
创建示例数据
在开始本文之前，我们需要先创建示例数据。我们将使用下面的SQL语句创建四个表格，并将它们构建成树形结构：
CREATE TABLE Department (
DeptID INT NOT NULL PRIMARY KEY,
DeptName VARCHAR(50) NOT NULL,
ParentDeptID INT NULL
);
CREATE TABLE Employees (
EmployeeID INT NOT NULL PRIMARY KEY,
EmployeeName VARCHAR(50) NOT NULL,
Salary INT NOT NULL,
DeptID INT NOT NULL
);
CREATE TABLE Projects (
ProjectID INT NOT NULL PRIMARY KEY,
ProjectName VARCHAR(50) NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
DeptID INT NOT NULL
);
CREATE TABLE EmployeesProjects (
EmployeeID INT NOT NULL,
ProjectID INT NOT NULL,
PRIMARY KEY (EmployeeID, ProjectID)
);
INSERT INTO Department (DeptID, DeptName, ParentDeptID) VALUES (1, 'Management', NULL);
INSERT INTO Department (DeptID, DeptName, ParentDeptID) VALUES (2, 'Human Resources', 1);
INSERT INTO Department (DeptID, DeptName, ParentDeptID) VALUES (3, 'Information Technology', 1);
INSERT INTO Department (DeptID, DeptName, ParentDeptID) VALUES (4, 'Marketing', 1);
INSERT INTO Department (DeptID, DeptName, ParentDeptID) VALUES (5, 'Recruiting', 2);
INSERT INTO Department (DeptID, DeptName, ParentDeptID) VALUES (6, 'Software Development', 3);
INSERT INTO Department (DeptID, DeptName, ParentDeptID) VALUES (7, 'Hardware Development', 3);
INSERT INTO Department (DeptID, DeptName, ParentDeptID) VALUES (8, 'Product Management', 4);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (1, 'John Smith', 50000, 1);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (2, 'Sarah Brown', 60000, 1);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (3, 'Mark Johnson', 45000, 2);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (4, 'Kim Lee', 65000, 2);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (5, 'Tom Wang', 55000, 3);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (6, 'Jerry Li', 75000, 3);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (7, 'Nina Kim', 55000, 4);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (8, 'Alex Chen', 65000, 4);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (9, 'Eva Zhang', 70000, 5);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (10, 'Sophie Liu', 60000, 5);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (11, 'Karen Wu', 80000, 6);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (12, 'Peter Chen', 70000, 6);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (13, 'Bob Wang', 90000, 7);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (14, 'Grace Li', 80000, 7);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (15, 'Alice Wang', 75000, 8);
INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DeptID) VALUES (16, 'David Lee', 85000, 8);
INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate, DeptID) VALUES (1, 'Project X', '20190101', '20190701', 3);
INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate, DeptID) VALUES (2, 'Project Y', '20190201', '20190601', 5);
INSERT INTO Projects (ProjectID, ProjectName, StartDate, EndDate, DeptID) VALUES (3, 'Project Z', '20190501', '20191201', 7);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (1, 1);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (2, 1);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (3, 2);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (4, 2);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (6, 1);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (6, 2);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (11, 1);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (11, 3);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (13, 3);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (14, 3);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (15, 3);
INSERT INTO EmployeesProjects (EmployeeID, ProjectID) VALUES (16, 3);
查询所有部门
我们可以使用以下SQL语句查询所有部门：
SELECT *
FROM Department;
这将返回整个Department表格的内容，如下所示：
DeptID DeptName ParentDeptID
------- ------------------------ -------------
1 Management NULL
2 Human Resources 1
3 Information Technology 1
4 Marketing 1
5 Recruiting 2
6 Software Development 3
7 Hardware Development 3
8 Product Management 4
查询所有部门以及它们的父部门
我们可以使用以下SQL语句查询所有部门以及它们的父部门：
WITH DepartmentCTE (DeptID, DeptName, ParentDeptID) AS (
SELECT DeptID, DeptName, ParentDeptID
FROM Department
WHERE ParentDeptID IS NULL
UNION ALL
SELECT d.DeptID, d.DeptName, d.ParentDeptID
FROM Department d
INNER JOIN DepartmentCTE cte ON d.ParentDeptID = cte.DeptID
)
SELECT *
FROM DepartmentCTE;
这将返回所有部门以及它们的父部门，如下所示：
DeptID DeptName ParentDeptID
------- ------------------------ -------------
1 Management NULL
2 Human Resources 1
3 Information Technology 1
4 Marketing 1
5 Recruiting 2
6 Software Development 3
7 Hardware Development 3
8 Product Management 4
查询所有部门及其员工
我们可以使用以下SQL语句查询所有部门及其员工：
WITH DepartmentCTE (DeptID, DeptName, ParentDeptID) AS (
SELECT DeptID, DeptName, ParentDeptID
FROM Department
WHERE ParentDeptID IS NULL
UNION ALL
SELECT d.DeptID, d.DeptName, d.ParentDeptID
FROM Department d
INNER JOIN DepartmentCTE cte ON d.ParentDeptID = cte.DeptID
)
SELECT d.DeptName AS Department, e.EmployeeName AS Employee
FROM DepartmentCTE d
LEFT JOIN Employees e ON d.DeptID = e.DeptID
ORDER BY d.DeptID;
这将返回所有部门及其员工，如下所示：
Department Employee
----------------------- ------------
Management John Smith
Management Sarah Brown
Human Resources Mark Johnson
Human Resources Kim Lee
Information Technology Tom Wang
Information Technology Jerry Li
Marketing Nina Kim
Marketing Alex Chen
Recruiting Eva Zhang
Recruiting Sophie Liu
Software Development Karen Wu
Software Development Peter Chen
Hardware Development Bob Wang
Hardware Development Grace Li
Product Management Alice Wang
Product Management David Lee
查询所有项目及其成员
我们可以使用以下SQL语句查询所有项目及其成员：
WITH ProjectCTE (ProjectID, ProjectName, DeptID) AS (
SELECT ProjectID, ProjectName, DeptID
FROM Projects
WHERE DeptID IS NOT NULL
UNION ALL
SELECT p.ProjectID, p.ProjectName, d.DeptID
FROM Projects p
INNER JOIN Department d ON p.DeptID = d.ParentDeptID
INNER JOIN ProjectCTE cte ON d.DeptID = cte.DeptID
)
SELECT p.ProjectName AS Project, e.EmployeeName AS Employee
FROM ProjectCTE p
INNER JOIN EmployeesProjects ep ON p.ProjectID = ep.ProjectID
INNER JOIN Employees e ON ep.EmployeeID = e.EmployeeID
ORDER BY p.ProjectName;
这将返回所有项目及其成员，如下所示：
Project Employee
----------- ------------
Project X John Smith
Project X Sarah Brown
Project X Jerry Li
Project Y Mark Johnson
Project Y Kim Lee
Project Y Jerry Li
Project Y Eva Zhang
Project Y Sophie Liu
Project Z Karen Wu
Project Z Bob Wang
Project Z Grace Li
Project Z Alice Wang
Project Z David Lee
创建一棵树形结构
我们可以使用以下SQL语句创建一棵树形结构：
WITH DepartmentCTE (DeptID, DeptName, ParentDeptID, LEVEL) AS (
SELECT DeptID, DeptName, ParentDeptID, 0 AS LEVEL
FROM Department
WHERE ParentDeptID IS NULL
UNION ALL
SELECT d.DeptID, d.DeptName, d.ParentDeptID, cte.LEVEL + 1
FROM Department d
INNER JOIN DepartmentCTE cte ON d.ParentDeptID = cte.DeptID
)
SELECT REPLICATE('-', LEVEL) + DeptName
FROM DepartmentCTE;
这将返回以下结果：
Management
-Human Resources
--Recruiting
-Information Technology
--Software Development
---Karen Wu
---Peter Chen
--Hardware Development
---Bob Wang
---Grace Li
-Marketing
--Alice Wang
--David Lee
--Product Management
---Alex Chen
结论
本文介绍了如何在MSSQL中实现多表树结构的查询和构建。我们可以使用WITH子句和CTE（公共表达式）来查询多个表格，并构建它们成树形结构。这种技术可用于各种应用场景，例如组织结构、产品层次结构等。实现多表树结构查询和构建可以提高应用程序的效率和可靠性，从而提供更好的用户体验。