选择每个 SQL Group By 组的第一行

如何使用示例选择每个 SQL Group By 组的第一行。对于此选择每个组的第一行示例，我们使用下面显示的数据。

Customer Table Data 1

SQL ROW_NUMBER 函数示例

SQL ROW_NUMBER 函数允许您为分区中的每个记录分配排名编号。在此示例中，我们将展示如何选择每个 SQL 组的第一行。以下查询将

  • 首先，按职业对数据进行分区，并使用年收入分配排名编号。
  • 接下来，ROW_NUMBER 将从每个组中选择第一行。
-- Using CTE to save the grouping data
WITH groups AS (
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
         	   ) AS [ROW NUMBER]
  FROM [Customers]
  )
SELECT * FROM groups
WHERE groups.[ROW NUMBER] = 1

让我向您展示 SQL Server 输出中的 Select 语句，它位于 CTE 中。

Select First Row in each Group By group 2

我们提取所有行号等于 1 的记录。

Select First Row in each Group By group 3

下面的语句使用其职业将数据划分为分区，然后我们按其 [年收入] 的降序对分区数据进行排序。从上面可以看出，我们有四个分区。

PARTITION BY [Occupation] 
ORDER BY [YearlyIncome] DESC

接下来，我们使用 ROW_NUMBER() 函数为记录分配排名。我建议您参考 ROW_NUMBER 文章。

ROW_NUMBER() OVER (
              PARTITION BY [Occupation] 
              ORDER BY [YearlyIncome] DESC
             ) AS [ROW NUMBER]

选择每个组的第一行示例 2

在此示例中，我们使用 子查询 来选择每个 Group By 组的第一行。

SELECT * FROM (
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,ROW_NUMBER() OVER (
                     PARTITION BY [Occupation] 
                     ORDER BY [YearlyIncome] DESC
         	   ) AS [ROW NUMBER]
  FROM [Customers]
  ) groups
WHERE groups.[ROW NUMBER] = 1
ORDER BY groups.YearlyIncome DESC
Select First Row in each Group By group 4