SQL Server 中的子句

在本文中，我们将通过实际示例向您展示 SQL Server 中的子句列表。这是您在面试中可能会遇到的基本面试问题之一。

SQL Server 中有五种类型的子句。它们是：

  • Order By 子句
  • Top 子句
  • Where 子句
  • Group By 子句和
  • Having 子句
employee Source 1

SQL Server 中的 Order By 子句

SQL Server Order By 子句用于按升序或降序对数据进行排序。建议您参考 Order By 文章以详细了解 SQL Order By。在此示例中，我们将使用销售额对 Employee 表进行降序排序

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  ORDER BY Sales DESC
Clauses Example 2

Top 子句

Top 用于选择前 N 条记录。请参阅 Top 子句。此示例返回 Employee 表中按年收入降序排列的前六条记录。

SELECT TOP 6 [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  ORDER BY YearlyIncome DESC
Top Example 3

SQL Server 中的 Where 子句

Where 子句用于限制 SELECT 语句返回的记录数。请参阅 Where 子句。此示例返回 Employee 表中销售额大于 2000 的所有记录

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
      ,[HireDate]
  FROM [Employee]
  WHERE Sales > 2000
Where Example 4

SQL Server 中的 Group By 子句

Group By 子句通过对一个或多个列进行分组并对剩余列应用聚合函数来返回聚合数据。

此 Group By 子句查询按 Occupation 对 Employee 表进行分组，并返回 Yearly Income 的总和、Sales 的总和、Sales 的平均值和 Yearly Income 的平均值

SELECT Occupation
      ,SUM([YearlyIncome]) AS TotalIncome
      ,SUM([Sales]) AS TotalSale
      ,AVG([YearlyIncome]) AS AverageIncome
      ,AVG([Sales]) AS AverageSale
  FROM [Employee]
GROUP BY Occupation
Group By Example 5

Having 子句

Having 子句用于限制 Group By 子句返回的记录数。

此查询首先按 Occupation 和 Education 对 Employee 表进行分组，并返回 Income、Sales 的总和、Average Yearly Income 和 Average Sales。接下来，Having 子句将限制销售额总和大于 2000 的记录

SELECT Occupation,Education
 	  ,SUM([YearlyIncome]) AS TotalIncome
      ,SUM([Sales]) AS TotalSale
      ,AVG([YearlyIncome]) AS AverageIncome
      ,AVG([Sales]) AS AverageSale
  FROM [Employee]
GROUP BY Occupation, Education
HAVING SUM(Sales) > 2000
Clauses Example 6