SQL Server函数全解析：从基础到高级应用

1. SQL Server函数全景概览作为微软企业级数据库解决方案的核心组件SQL Server提供了超过300个内置函数这些函数构成了T-SQL语言的基础工具集。在我15年的数据库开发生涯中这些函数就像瑞士军刀一样每天都会在查询优化、数据清洗和报表生成中反复使用。函数主要分为四大类日期时间函数处理时间维度计算字符串函数完成文本操作数学函数执行数值运算聚合函数实现数据统计分析。值得注意的是不同版本的SQL Server函数集会有所差异比如SQL Server 2012引入了FORMAT()等现代函数而SQL Server 2016开始支持STRING_SPLIT()这类实用函数。提示使用函数前务必确认SQL Server版本支持情况可通过SELECT VERSION查询当前版本号。2. 日期时间函数深度解析2.1 基础日期转换日期处理是数据库操作中最常见的需求之一。GETDATE()函数获取当前系统日期时间这是大多数日期计算的起点SELECT GETDATE() AS CurrentDateTime; -- 输出2023-08-20 14:35:22.123CONVERT()函数是日期格式化的主力工具其语法为CONVERT(data_type, expression [, style])常用style代码示例101MM/DD/YYYY美国标准112YYYYMMDDISO格式120YYYY-MM-DD HH:MI:SSODBC规范SELECT CONVERT(VARCHAR, GETDATE(), 101) AS USFormat, CONVERT(VARCHAR, GETDATE(), 112) AS ISOFormat, CONVERT(VARCHAR, GETDATE(), 120) AS ODBCFormat;2.2 高级日期计算DATEADD()函数实现日期加减运算我在财务系统季度报表中经常使用-- 计算三个月后的日期 SELECT DATEADD(MONTH, 3, 2023-08-20) AS ThreeMonthsLater; -- 项目计划中常用的工作日计算排除周末 DECLARE StartDate DATE 2023-08-01; DECLARE DaysToAdd INT 10; WHILE DaysToAdd 0 BEGIN SET StartDate DATEADD(DAY, 1, StartDate); IF DATEPART(WEEKDAY, StartDate) NOT IN (1, 7) SET DaysToAdd DaysToAdd - 1; END SELECT StartDate AS BusinessDay;DATEDIFF()函数计算时间间隔在用户留存分析中特别有用-- 计算用户注册到首次购买的天数 SELECT UserID, DATEDIFF(DAY, RegisterDate, FirstPurchaseDate) AS DaysToFirstPurchase FROM Users;2.3 日期部分提取DATEPART()和DATENAME()函数可以提取日期的特定部分SELECT DATEPART(YEAR, GETDATE()) AS CurrentYear, DATENAME(MONTH, GETDATE()) AS MonthName, DATEPART(QUARTER, GETDATE()) AS FiscalQuarter;EOMONTH()函数SQL Server 2012能智能返回月末日期在财务周期计算中非常实用-- 获取当前月份的最后一天 SELECT EOMONTH(GETDATE()) AS MonthEnd; -- 计算信用卡账单周期当月20日到下月19日 DECLARE Today DATE GETDATE(); DECLARE BillingStart DATE DATEFROMPARTS( YEAR(Today), MONTH(Today), 20); IF DAY(Today) 20 SET BillingStart DATEADD(MONTH, -1, BillingStart); SELECT BillingStart AS BillingStart, DATEADD(DAY, -1, DATEADD(MONTH, 1, BillingStart)) AS BillingEnd;3. 字符串函数完全指南3.1 基础字符串操作CONCAT()函数替代传统的运算符能自动处理NULL值SELECT CONCAT(Hello, NULL, World) AS Greeting; -- 输出HelloWorld而非NULLSUBSTRING()函数配合LEN()实现安全截取-- 安全获取前10个字符避免越界错误 DECLARE Text VARCHAR(100) 这是一段示例文本; SELECT SUBSTRING(Text, 1, CASE WHEN LEN(Text) 10 THEN 10 ELSE LEN(Text) END) AS ShortText;3.2 高级字符串处理STRING_SPLIT()函数SQL Server 2016实现字符串拆分-- 拆分CSV字符串为表格 SELECT value AS Item FROM STRING_SPLIT(苹果,香蕉,橙子, ,); -- 实际应用标签系统查询 DECLARE Tags NVARCHAR(100) 科技,数据库,微软; SELECT b.* FROM Blogs b JOIN STRING_SPLIT(Tags, ,) t ON b.Tags LIKE % t.value %;STRING_AGG()函数SQL Server 2017实现逆向聚合-- 将多行合并为CSV字符串 SELECT DepartmentID, STRING_AGG(EmployeeName, , ) AS TeamMembers FROM Employees GROUP BY DepartmentID;3.3 字符串搜索与替换PATINDEX()支持通配符模式匹配-- 查找第一个电子邮件地址的位置 DECLARE Text NVARCHAR(500) 联系我们supportexample.com 或 salesexample.org; SELECT PATINDEX(%[A-Z0-9._%-][A-Z0-9.-]\.[A-Z]{2,}%, Text) AS EmailPosition;REPLACE()与TRANSLATE()函数对比-- REPLACE是直接替换 SELECT REPLACE(1-800-555-1234, -, ) AS PhoneWithSpaces; -- TRANSLATE(SQL Server 2017)是字符级映射 SELECT TRANSLATE(1-800-555-1234, -, .) AS PhoneWithDots;4. 数学函数精要4.1 基础数学运算ROUND()函数的银行家舍入规则-- 四舍六入五成双 SELECT ROUND(12.5, 0) AS Example1, -- 12 ROUND(13.5, 0) AS Example2; -- 14RAND()函数生成随机数的正确用法-- 生成10-20之间的随机整数 SELECT FLOOR(RAND() * 11) 10 AS RandomNumber; -- 常见错误在同一个批处理中多次调用RAND()会返回相同值 -- 正确做法使用NEWID()作为种子 SELECT ABS(CHECKSUM(NEWID())) % 100 1 AS TrueRandom;4.2 高级数学函数统计学函数在数据分析中的应用-- 计算移动平均3期 SELECT Date, Sales, AVG(Sales) OVER (ORDER BY Date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg FROM SalesData;三角函数处理地理空间数据-- 计算两点间距离Haversine公式简化版 DECLARE Lat1 FLOAT 39.9042, Lon1 FLOAT 116.4074; -- 北京 DECLARE Lat2 FLOAT 31.2304, Lon2 FLOAT 121.4737; -- 上海 SELECT 6371 * ACOS( SIN(RADIANS(Lat1)) * SIN(RADIANS(Lat2)) COS(RADIANS(Lat1)) * COS(RADIANS(Lat2)) * COS(RADIANS(Lon1 - Lon2)) ) AS DistanceInKM;5. 聚合函数高级技巧5.1 标准聚合函数COUNT()函数的三种用法差异-- 计算总行数包括NULL SELECT COUNT(*) AS TotalRows FROM Orders; -- 计算非NULL值的行数 SELECT COUNT(CustomerID) AS CustomerCount FROM Orders; -- 计算唯一值数量 SELECT COUNT(DISTINCT ProductID) AS UniqueProducts FROM OrderDetails;GROUPING SETS实现多维分析-- 同时按年、月、季度聚合销售数据 SELECT YEAR(OrderDate) AS OrderYear, MONTH(OrderDate) AS OrderMonth, DATEPART(QUARTER, OrderDate) AS OrderQuarter, SUM(Amount) AS TotalAmount FROM Orders GROUP BY GROUPING SETS ( (YEAR(OrderDate)), (YEAR(OrderDate), MONTH(OrderDate)), (YEAR(OrderDate), DATEPART(QUARTER, OrderDate)) ) ORDER BY OrderYear, OrderMonth;5.2 窗口函数应用ROW_NUMBER() vs RANK() vs DENSE_RANK()-- 销售排名示例 SELECT SalesPerson, SalesAmount, ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS RowNum, RANK() OVER (ORDER BY SalesAmount DESC) AS RankVal, DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS DenseRank FROM Sales ORDER BY SalesAmount DESC;累计计算模式-- 计算累计销售额和移动平均 SELECT OrderDate, DailySales, SUM(DailySales) OVER (ORDER BY OrderDate) AS RunningTotal, AVG(DailySales) OVER (ORDER BY OrderDate ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS MonthlyMovingAvg FROM ( SELECT CAST(OrderDate AS DATE) AS OrderDate, SUM(Amount) AS DailySales FROM Orders GROUP BY CAST(OrderDate AS DATE) ) AS DailyData;6. 函数性能优化实践6.1 常见性能陷阱标量函数在WHERE子句中的性能问题-- 错误做法导致全表扫描 SELECT * FROM Orders WHERE YEAR(OrderDate) 2023 AND MONTH(OrderDate) 8; -- 正确做法使用范围查询 SELECT * FROM Orders WHERE OrderDate 2023-08-01 AND OrderDate 2023-09-01;多函数嵌套的计算成本-- 低效写法 SELECT UPPER(LEFT(REPLACE(ProductName, , _), 20)) AS ProductCode FROM Products; -- 优化方案计算列索引 ALTER TABLE Products ADD ProductCode AS UPPER(LEFT(REPLACE(ProductName, , _), 20)) PERSISTED; CREATE INDEX IX_Products_ProductCode ON Products(ProductCode);6.2 最佳实践建议函数与索引的配合-- 创建函数索引的替代方案 CREATE INDEX IX_Orders_OrderDate ON Orders(OrderDate); -- 使用包含列减少书签查找 CREATE INDEX IX_Customers_Name ON Customers(LastName, FirstName) INCLUDE (Email, Phone);TVF表值函数的合理使用-- 内联TVF性能更好 CREATE FUNCTION dbo.GetCustomerOrders(CustomerID INT) RETURNS TABLE AS RETURN ( SELECT OrderID, OrderDate, Amount FROM Orders WHERE CustomerID CustomerID ); -- 调用方式 SELECT * FROM dbo.GetCustomerOrders(12345);7. 版本差异与兼容性处理7.1 新旧版本函数对照日期格式化方案演进-- SQL Server 2012之前 SELECT CONVERT(VARCHAR, GETDATE(), 120) AS ODBCFormat; -- SQL Server 2012 SELECT FORMAT(GETDATE(), yyyy-MM-dd HH:mm:ss) AS ISOFormat; -- 兼容性写法 DECLARE Date DATETIME GETDATE(); SELECT CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY(ProductVersion)) 11.0 THEN FORMAT(Date, yyyy-MM-dd HH:mm:ss) ELSE CONVERT(VARCHAR, Date, 120) END AS FormattedDate;7.2 自定义兼容函数创建跨版本兼容的字符串分割函数CREATE FUNCTION dbo.SplitString ( String NVARCHAR(MAX), Delimiter CHAR(1) ) RETURNS Results TABLE (Item NVARCHAR(MAX)) AS BEGIN IF CONVERT(VARCHAR(128), SERVERPROPERTY(ProductVersion)) 13.0 BEGIN INSERT INTO Results SELECT value FROM STRING_SPLIT(String, Delimiter); END ELSE BEGIN -- XML方法兼容旧版本 DECLARE XML XML CAST(i REPLACE(String, Delimiter, /ii) /i AS XML); INSERT INTO Results SELECT t.i.value(., NVARCHAR(MAX)) FROM XML.nodes(//i) AS t(i) WHERE t.i.value(., NVARCHAR(MAX)) ; END RETURN; END;8. 实战案例销售分析系统8.1 数据准备与清洗使用字符串函数规范产品编号-- 清理产品编号中的非法字符 UPDATE Products SET ProductCode REPLACE( TRANSLATE( UPPER(ProductCode), !#$%^*()_-[]{}|;:,./?, ), , ) WHERE ProductCode LIKE %[^A-Z0-9]%;日期维度表生成-- 创建日期维度表 DECLARE StartDate DATE 2020-01-01; DECLARE EndDate DATE 2030-12-31; WITH DateCTE AS ( SELECT StartDate AS DateValue UNION ALL SELECT DATEADD(DAY, 1, DateValue) FROM DateCTE WHERE DATEADD(DAY, 1, DateValue) EndDate ) INSERT INTO DimDate SELECT CONVERT(CHAR(8), DateValue, 112) AS DateKey, DateValue AS FullDate, DATEPART(YEAR, DateValue) AS CalendarYear, DATENAME(MONTH, DateValue) AS MonthName, DATEPART(QUARTER, DateValue) AS CalendarQuarter, DATEPART(WEEK, DateValue) AS WeekOfYear, DATEPART(WEEKDAY, DateValue) AS DayOfWeek FROM DateCTE OPTION (MAXRECURSION 10000);8.2 多维度销售分析使用ROLLUP实现多层次聚合-- 按产品类别和年月的销售分析 SELECT p.CategoryName, YEAR(o.OrderDate) AS OrderYear, MONTH(o.OrderDate) AS OrderMonth, SUM(od.Quantity * od.UnitPrice) AS TotalSales FROM OrderDetails od JOIN Orders o ON od.OrderID o.OrderID JOIN Products p ON od.ProductID p.ProductID GROUP BY ROLLUP(p.CategoryName, YEAR(o.OrderDate), MONTH(o.OrderDate)) ORDER BY p.CategoryName, OrderYear, OrderMonth;PIVOT实现交叉报表-- 按产品类别的年度销售透视表 SELECT CategoryName, [2020], [2021], [2022], [2023] FROM ( SELECT p.CategoryName, YEAR(o.OrderDate) AS OrderYear, od.Quantity * od.UnitPrice AS SalesAmount FROM OrderDetails od JOIN Orders o ON od.OrderID o.OrderID JOIN Products p ON od.ProductID p.ProductID ) AS SourceData PIVOT ( SUM(SalesAmount) FOR OrderYear IN ([2020], [2021], [2022], [2023]) ) AS PivotTable;9. 函数调试与错误处理9.1 常见错误排查日期转换错误处理-- 安全日期转换函数 CREATE FUNCTION dbo.SafeConvertToDate ( DateString VARCHAR(50), FormatStyle INT 120 ) RETURNS DATE AS BEGIN DECLARE Result DATE; BEGIN TRY SET Result CONVERT(DATE, DateString, FormatStyle); END TRY BEGIN CATCH SET Result NULL; END CATCH RETURN Result; END;字符串截断问题预防-- 动态调整字符串长度 DECLARE LongText VARCHAR(MAX) REPLICATE(X, 10000); DECLARE MaxLength INT 8000; SELECT CASE WHEN LEN(LongText) MaxLength THEN LEFT(LongText, MaxLength) ... ELSE LongText END AS SafeText;9.2 性能监控与优化函数执行计划分析-- 检查函数导致的隐式转换 SELECT OBJECT_NAME(st.objectid) AS ObjectName, cp.usecounts AS ExecutionCount, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp WHERE st.text LIKE %CONVERT(% OR st.text LIKE %CAST(%;统计函数使用频率-- 查找最常用的函数 SELECT OBJECT_NAME(object_id) AS ObjectName, definition AS Definition, LEN(definition) - LEN(REPLACE(definition, GETDATE(), )) AS GetDateCount, LEN(definition) - LEN(REPLACE(definition, CONVERT(, )) AS ConvertCount FROM sys.sql_modules WHERE definition LIKE %GETDATE()% OR definition LIKE %CONVERT(% ORDER BY GetDateCount ConvertCount DESC;10. 扩展应用与创新用法10.1 JSON与XML处理现代SQL Server的JSON函数-- 解析JSON订单数据 DECLARE OrderJSON NVARCHAR(MAX) { OrderID: 12345, Customer: {ID: 678, Name: 张三}, Items: [ {ProductID: 101, Quantity: 2}, {ProductID: 205, Quantity: 1} ] }; SELECT JSON_VALUE(OrderJSON, $.OrderID) AS OrderID, JSON_VALUE(OrderJSON, $.Customer.Name) AS CustomerName, j.ProductID, j.Quantity FROM OPENJSON(OrderJSON, $.Items) WITH ( ProductID INT $.ProductID, Quantity INT $.Quantity ) AS j;XML数据处理技巧-- 将表格数据转为XML SELECT ProductID, ProductName, UnitPrice FROM Products FOR XML PATH(Product), ROOT(Products); -- 解析XML订单 DECLARE OrderXML XML Order Header OrderID1001/OrderID OrderDate2023-08-20/OrderDate /Header Items Item ProductID101 Quantity2/ Item ProductID205 Quantity1/ /Items /Order; SELECT OrderXML.value((/Order/Header/OrderID)[1], INT) AS OrderID, i.Item.value(ProductID, INT) AS ProductID, i.Item.value(Quantity, INT) AS Quantity FROM OrderXML.nodes(/Order/Items/Item) AS i(Item);10.2 动态SQL与函数结合安全构建动态查询CREATE PROCEDURE dbo.SearchProducts ProductName NVARCHAR(100) NULL, CategoryID INT NULL, MinPrice DECIMAL(18,2) NULL, MaxPrice DECIMAL(18,2) NULL AS BEGIN DECLARE SQL NVARCHAR(MAX) N SELECT ProductID, ProductName, CategoryID, UnitPrice FROM Products WHERE 1 1; IF ProductName IS NOT NULL SET SQL SQL N AND ProductName LIKE % REPLACE(ProductName, , ) %; IF CategoryID IS NOT NULL SET SQL SQL N AND CategoryID CAST(CategoryID AS NVARCHAR); IF MinPrice IS NOT NULL SET SQL SQL N AND UnitPrice CAST(MinPrice AS NVARCHAR); IF MaxPrice IS NOT NULL SET SQL SQL N AND UnitPrice CAST(MaxPrice AS NVARCHAR); EXEC sp_executesql SQL; END;函数式编程风格-- 链式函数处理 CREATE FUNCTION dbo.FormatPhoneNumber ( Phone VARCHAR(50) ) RETURNS VARCHAR(50) AS BEGIN -- 移除所有非数字字符 DECLARE Digits VARCHAR(50) REPLACE( TRANSLATE(Phone, ()- ., ), , ); -- 格式化(XXX) XXX-XXXX RETURN CASE WHEN LEN(Digits) 10 THEN ( LEFT(Digits, 3) ) SUBSTRING(Digits, 4, 3) - RIGHT(Digits, 4) ELSE Digits END; END;