利用MSSQL视图设计一对多关系

Views are used to represent the data stored in a database in table form. View can be used to derive data from different tables, change the data format and join data from more than one database. By using views, we can create complex relationships between tables that are otherwise impossible to do.

In SQL Server, we can create views to establish one-to-many relationships between two tables. A one-to-many relationship is a common scenario where one table is associated with many other tables. For example, a book table may be related to a chapter table, where each book has many chapters.

In this article, we’ll use the AdventureWorks database to demonstrate how to create a one-to-many view. We will use the [HumanResources].[Department], [HumanResources].[Employee] and [HumanResources].[EmployeeDepartmentHistory] tables to create a view showing a many-to-one relationship between departments and employees.

The first step is to create the view. To do this, open SQL Server Management Studio, connect to the AdventureWorks database and run the following command:

CREATE VIEW [dbo].[vw_OneToMany]

AS

SELECT

d.DepartmentId,

d.Name AS DepartmentName,

e.EmployeeId,

e.FirstName + ‘ ‘ + e.LastName AS EmployeeName

FROM

[HumanResources].[Department] d

INNER JOIN

[HumanResources].[EmployeeDepartmentHistory] edh ON d.DepartmentId = edh.DepartmentId

INNER JOIN

[HumanResources].[Employee] e ON edh.BusinessEntityId = e.BusinessEntityId

The view will show all the departments, the corresponding employee ID and the employee’s name. The results are displayed in the following figure:

We can also add criteria to restrict the view. For example, let’s say we want to display all the employees that are part of the Sales department. We can modify the view to include the department name in the WHERE clause as follows:

CREATE VIEW [dbo].[vw_OneToMany]

AS

SELECT

d.DepartmentId,

d.Name AS DepartmentName,

e.EmployeeId,

e.FirstName + ‘ ‘ + e.LastName AS EmployeeName

FROM

[HumanResources].[Department] d

INNER JOIN

[HumanResources].[EmployeeDepartmentHistory] edh ON d.DepartmentId = edh.DepartmentId

INNER JOIN

[HumanResources].[Employee] e ON edh.BusinessEntityId = e.BusinessEntityId

WHERE

d.Name = ‘Sales’

The view now only displays the employees that are part of the Sales department. Using views to create one-to-many relationships makes it easy to re-use complex queries and keep your database schema clean and organized.

本文来源：https://www.yuntue.com/post/86433.html | 云服务器网，转载请注明出处！