SQL Server高级函数的用法有哪些

发布时间：2021-11-30 10:50:09 作者：柒染
来源：亿速云阅读：161

# SQL Server高级函数的用法有哪些

## 引言

SQL Server作为一款强大的关系型数据库管理系统，提供了丰富的高级函数来满足复杂数据处理需求。这些函数不仅能简化查询逻辑，还能显著提升数据处理效率。本文将深入探讨SQL Server中常用的高级函数及其应用场景。

---

## 一、窗口函数（Window Functions）

### 1. ROW_NUMBER()
```sql
SELECT 
    ROW_NUMBER() OVER(ORDER BY SalesAmount DESC) AS Rank,
    ProductName,
    SalesAmount
FROM SalesData

功能：为结果集中的行分配唯一序号
典型应用：分页查询、数据去重

2. RANK()与DENSE_RANK()

SELECT 
    ProductID,
    RANK() OVER(PARTITION BY CategoryID ORDER BY Price DESC) AS PriceRank,
    DENSE_RANK() OVER(PARTITION BY CategoryID ORDER BY Price DESC) AS DensePriceRank
FROM Products

区别：
- RANK()：相同值具有相同排名，后续排名会跳过
- DENSE_RANK()：相同值具有相同排名，但后续排名不跳过

3. NTILE()

SELECT 
    CustomerID,
    NTILE(4) OVER(ORDER BY TotalPurchases DESC) AS Quartile
FROM Customers

用途：将结果集分成指定数量的桶

二、分析函数（Analytic Functions）

1. LEAD()和LAG()

SELECT 
    OrderDate,
    SalesAmount,
    LAG(SalesAmount, 1) OVER(ORDER BY OrderDate) AS PrevDaySales,
    LEAD(SalesAmount, 1) OVER(ORDER BY OrderDate) AS NextDaySales
FROM DailySales

作用：访问当前行之前/之后的行的值

2. FIRST_VALUE()和LAST_VALUE()

SELECT 
    Department,
    EmployeeName,
    Salary,
    FIRST_VALUE(EmployeeName) OVER(PARTITION BY Department ORDER BY Salary DESC) AS TopEarner
FROM Employees

三、聚合函数的增强用法

1. GROUPING SETS

SELECT 
    ProductCategory,
    Region,
    SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS(
    (ProductCategory, Region),
    (ProductCategory),
    (Region),
    ()
)

2. CUBE和ROLLUP

-- CUBE生成所有可能的组合
SELECT 
    Year, Quarter, SUM(Sales)
FROM Sales
GROUP BY CUBE(Year, Quarter)

-- ROLLUP生成层次结构聚合
SELECT 
    Country, Region, City, SUM(Population)
FROM Locations
GROUP BY ROLLUP(Country, Region, City)

四、JSON处理函数（SQL Server 2016+）

1. JSON_VALUE()

SELECT 
    OrderID,
    JSON_VALUE(OrderDetails, '$.Customer.Name') AS CustomerName
FROM Orders

2. JSON_MODIFY()

UPDATE Products
SET ProductSpec = JSON_MODIFY(ProductSpec, '$.Weight', '2.5kg')
WHERE ProductID = 1001

3. OPENJSON()

SELECT *
FROM OPENJSON(@jsonVariable)
WITH (
    ProductID int '$.id',
    ProductName nvarchar(100) '$.name'
)

五、字符串处理函数

1. STRING_AGG()（SQL Server 2017+）

SELECT 
    DepartmentID,
    STRING_AGG(EmployeeName, ', ') AS TeamMembers
FROM Employees
GROUP BY DepartmentID

2. CONCAT_WS()

SELECT 
    CONCAT_WS(' - ', FirstName, LastName, Email) AS ContactInfo
FROM Contacts

3. TRIM()（SQL Server 2017+）

UPDATE Products
SET Description = TRIM(Description)

六、时间智能函数

1. DATEFROMPARTS()系列

SELECT DATETIMEFROMPARTS(2023, 12, 31, 23, 59, 59, 0) AS NewYearEve

2. EOMONTH()

SELECT EOMONTH(GETDATE()) AS MonthEnd

3. DATE_BUCKET()（SQL Server 2022+）

SELECT 
    DATE_BUCKET(WEEK, 1, OrderDate) AS WeekBucket,
    SUM(OrderTotal) AS WeeklySales
FROM Orders
GROUP BY DATE_BUCKET(WEEK, 1, OrderDate)

七、系统元数据函数

1. SESSION_CONTEXT()

-- 设置会话上下文
EXEC sp_set_session_context 'user_id', 12345

-- 获取值
SELECT SESSION_CONTEXT(N'user_id') AS CurrentUserID

2. OBJECT_DEFINITION()

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.usp_GetOrders')) AS ProcedureDefinition

八、安全相关函数

1. HAS_PERMS_BY_NAME()

SELECT 
    HAS_PERMS_BY_NAME('Sales.Invoices', 'OBJECT', 'INSERT') AS CanInsertInvoices

2. ENCRYPTBYKEY()/DECRYPTBYKEY()

-- 加密
UPDATE Users
SET CreditCardNumber = ENCRYPTBYKEY(KEY_GUID('CreditCardKey'), CardNumber)

-- 解密
SELECT 
    UserID,
    CONVERT(nvarchar, DECRYPTBYKEY(CreditCardNumber)) AS DecryptedCard
FROM Users

九、高级应用场景

1. 递归CTE与窗口函数结合

WITH EmployeeHierarchy AS (
    -- 基础查询
    SELECT 
        EmployeeID,
        ManagerID,
        1 AS Level,
        CAST(EmployeeName AS VARCHAR(1000)) AS HierarchyPath
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    -- 递归部分
    SELECT 
        e.EmployeeID,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.HierarchyPath + ' > ' + e.EmployeeName AS VARCHAR(1000))
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy

2. 动态数据透视

DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sql NVARCHAR(MAX);

-- 动态获取列名
SELECT @columns = STRING_AGG(QUOTENAME(Year), ',')
FROM (SELECT DISTINCT Year FROM SalesData) AS Years;

-- 构建动态SQL
SET @sql = N'
SELECT ProductCategory, ' + @columns + '
FROM (
    SELECT 
        ProductCategory, 
        Year, 
        SalesAmount
    FROM SalesData
) AS SourceData
PIVOT (
    SUM(SalesAmount) FOR Year IN (' + @columns + ')
) AS PivotTable';

EXEC sp_executesql @sql;

结语

SQL Server的高级函数为数据处理提供了强大的工具集。掌握这些函数可以： 1. 显著减少客户端处理逻辑 2. 提高查询性能 3. 实现复杂业务需求 4. 简化SQL代码维护

建议在实际项目中根据具体需求选择合适的函数组合，并注意不同SQL Server版本对函数的支持差异。 “`

注：本文实际约1700字，已涵盖SQL Server主要高级函数类别。如需扩展特定函数细节或增加示例，可进一步补充内容。