row_number()在SQL Server中的應(yīng)用示例有哪些

sql
小樊
82
2024-08-17 23:17:36
欄目: 云計(jì)算

  1. 使用row_number()函數(shù)為查詢結(jié)果集中的每一行分配一個(gè)唯一的行號(hào)，常用于分頁(yè)查詢中。例如：
SELECT 
    ROW_NUMBER() OVER (ORDER BY ProductID) AS RowNum,
    ProductID,
    ProductName,
    UnitPrice
FROM Products
  1. 可以結(jié)合PARTITION BY子句將結(jié)果集分組，每個(gè)分組從1開(kāi)始計(jì)數(shù)。例如：
SELECT 
    ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ProductID) AS RowNum,
    ProductID,
    ProductName,
    CategoryID
FROM Products
  1. 使用row_number()函數(shù)來(lái)查找每個(gè)分組中的前N個(gè)記錄。例如，以下查詢將在每個(gè)類別中找到前兩個(gè)產(chǎn)品：
SELECT 
    *
FROM (
    SELECT 
        ProductID,
        ProductName,
        CategoryID,
        ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY ProductID) AS RowNum
    FROM Products
) AS RankedProducts
WHERE RowNum <= 2
  1. 可以在row_number()函數(shù)中使用ORDER BY子句來(lái)指定排序規(guī)則。例如，以下查詢按UnitPrice從高到低對(duì)產(chǎn)品進(jìn)行排序：
SELECT 
    ROW_NUMBER() OVER (ORDER BY UnitPrice DESC) AS RowNum,
    ProductID,
    ProductName,
    UnitPrice
FROM Products

0 踩