SQL 派生表

SQL Server 派生表不过是 From 子句中使用的子查询。在此示例中，我们将在数据库中使用两个表（员工详细信息和部门）。员工详细信息表中的数据是

部门表中的数据是

SQL 派生表示例

这是一个简单的示例，用于演示派生表。以下 SQL 查询将显示员工表中销售额大于 500 的所有列。

SELECT * FROM
(
	SELECT [EmpID]
		,[FirstName]
		,[LastName]
		,[Education]
		,[YearlyIncome]
		,[Sales]
		,[DeptID]
	FROM [EmployeeDetails]
) AS [Derived Employee Details]
WHERE [Sales] > 500

第一个 SELECT * 语句从内部 SELECT 语句或子查询派生列。

Derived Table 3

示例 2

在此派生表示例中，我们将在其中使用连接概念。以下查询将显示员工和部门表中销售额大于 500 且年收入大于或等于 60000 的所有匹配列。对于此派生表演示，我们使用 INNER JOIN。

SELECT * FROM
	(
		SELECT [EmpID]
			,[FirstName]
			,[LastName]
			,[Education]
			,[YearlyIncome]
			,[Sales]
			,[DepartmentName]
		FROM [EmployeeDetails]
		INNER JOIN [Department]
			ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
	) AS [Derived Employee Details]
WHERE [Sales] > 500 AND [YearlyIncome] >= 60000
Derived Table 4

SQL Server 派生表示例 3

以下查询计算每个部门的员工总数、销售额和年薪。

对于此示例，我们使用内连接从员工和部门表中获取一列。接下来，我们使用GROUP BY 子句来聚合列。请参阅聚合函数和连接文章。

SELECT * FROM
(
	SELECT  [DepartmentName]
		,COUNT(DepartmentName) AS [Total Employees in this Department]
		,SUM([YearlyIncome]) AS [Total Income]
		,SUM([Sales]) AS [Total Sale]
	FROM [EmployeeDetails]
	INNER JOIN [Department]
	ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
        GROUP BY [DepartmentName]
) AS [Derived Employee Details]
Derived Table Example 5

在此派生表示例中，我们只添加 WHERE 子句来限制员工。它显示员工计数大于 1 的部门名称。

SELECT * FROM
	(
		SELECT  [DepartmentName]
		  ,COUNT(DepartmentName) AS [Total Employees in this Department]
			,SUM([YearlyIncome]) AS [Total Income]
			,SUM([Sales]) AS [Total Sale]
		FROM [EmployeeDetails]
		INNER JOIN [Department]
		ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
		GROUP BY [DepartmentName]
	) AS [Derived Employee Details]
WHERE [Total Employees in this Department] > 1
Derived Table Example 6

以下派生表查询显示员工总收入大于 100000 的部门名称。

SELECT * FROM
	(
		SELECT  [DepartmentName]
		  ,COUNT(DepartmentName) AS [Total Employees in this Department]
			,SUM([YearlyIncome]) AS [Total Income]
			,SUM([Sales]) AS [Total Sale]
		FROM [EmployeeDetails]
		INNER JOIN [Department]
		ON [EmployeeDetails].[DeptID] = [Department].[DeptID]
		GROUP BY [DepartmentName]
	) AS [Derived Employee Details]
WHERE [Total Income] > 100000

评论已关闭。