16. SQLServer : 函数 表值函数内联表值函数{alert type="info"}IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL DROP FUNCTION Sales.ufn_SalesByStore; GO CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE AS RETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name );{/alert}多语句表值函数{alert type="info"}IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL DROP FUNCTION dbo.ufn_FindReports; GO CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER) RETURNS @retFindReports TABLE ( EmployeeID int primary key NOT NULL, FirstName nvarchar(255) NOT NULL, LastName nvarchar(255) NOT NULL, JobTitle nvarchar(50) NOT NULL, RecursionLevel int NOT NULL ) --Returns a result set that lists all the employees who report to the --specific employee directly or indirectly.*/ AS BEGIN WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns AS ( SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n FROM HumanResources.Employee e INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID WHERE e.BusinessEntityID = @InEmpID UNION ALL SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor FROM HumanResources.Employee e INNER JOIN EMP_cte ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID ) -- copy the required columns to the result of the function INSERT @retFindReports SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel FROM EMP_cte RETURN END; GO{/alert}标量函数{alert type="info"}IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL DROP FUNCTION ufnGetInventoryStock; GO CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int) RETURNS int AS -- Returns the stock level for the product. BEGIN DECLARE @ret int; SELECT @ret = SUM(p.Quantity) FROM Production.ProductInventory p WHERE p.ProductID = @ProductID AND p.LocationID = '6'; IF (@ret IS NULL) SET @ret = 0; RETURN @ret; END;SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply FROM Production.Product WHERE ProductModelID BETWEEN 75 and 80;{/alert}系统函数

9. SQLServer : 语言元素-控制流 BEGIN...END{callout color="#60ef4d"}包括一系列的 Transact-SQL 语句，从而可以执行一组 Transact-SQL 语句。 BEGIN 和 END 是控制流语言的关键字。{/callout}{alert type="success"}-- 语法 BEGIN { sql_statement | statement_block } END --示例 USE AdventureWorks2012 GO BEGIN TRANSACTION GO IF @@TRANCOUNT = 0 BEGIN SELECT FirstName, MiddleName FROM Person.Person WHERE LastName = 'Adams'; ROLLBACK TRANSACTION; PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transaction.'; GO /* Rolled back the transaction. */ {/alert}BREAK{callout color="#4defed"}BREAK 将退出当前 WHILE 循环。 如果当前循环嵌套在另一个循环中，BREAK 将只退出当前循环，并且将控制给予外部循环中的下一个语句。{/callout}{alert type="info"}WHILE (1=1) BEGIN IF EXISTS (SELECT * FROM ##MyTempTable WHERE EventCode = 'Done') BEGIN BREAK; -- 'Done' row has finally been inserted and detected, so end this loop. END PRINT N'The other process is not yet done.'; -- Re-confirm the non-done status to the console. WAITFOR DELAY '00:01:30'; -- Sleep for 90 seconds. END{/alert}ELSE (IF...ELSE){alert type="success"}-- 语法 IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] -- 示例一 IF 1 = 2 PRINT 'Boolean_expression is true.' ELSE PRINT 'Boolean_expression is false.' ; GO -- 示例二 USE AdventureWorks2012; GO IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5 PRINT 'There are more than 5 Touring-3000 bicycles.' ELSE PRINT 'There are 5 or less Touring-3000 bicycles.' ; GO -- 示例三 USE AdventureWorks2012; GO DECLARE @AvgWeight DECIMAL(8,2), @BikeCount INT IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5 BEGIN SET @BikeCount = (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); PRINT 'There are ' + CAST(@BikeCount AS VARCHAR(3)) + ' Touring-3000 bikes.' PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS VARCHAR(8)) + '.'; END ELSE BEGIN SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ); PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS VARCHAR(8)) + '.' ; END ; GO -- 示例四 DECLARE @Number INT; SET @Number = 50; IF @Number > 100 PRINT 'The number is large.'; ELSE BEGIN IF @Number < 10 PRINT 'The number is small.'; ELSE PRINT 'The number is medium.'; END ; GO {/alert}GOTO{callout color="#4d75ef"}将执行流更改到标签处。 跳过 GOTO 后面的 Transact-SQL 语句，并从标签位置继续处理。 GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。 GOTO 语句可嵌套使用。{/callout}{alert type="success"}-- 语法 Define the label: label: Alter the execution: GOTO label -- 示例 DECLARE @Counter int; SET @Counter = 1; WHILE @Counter < 10 BEGIN SELECT @Counter SET @Counter = @Counter + 1 IF @Counter = 4 GOTO Branch_One --Jumps to the first branch. IF @Counter = 5 GOTO Branch_Two --This will never execute. END Branch_One: SELECT 'Jumping To Branch One.' GOTO Branch_Three; --This will prevent Branch_Two from executing. Branch_Two: SELECT 'Jumping To Branch Two.' Branch_Three: SELECT 'Jumping To Branch Three.'; {/alert}RETURN{callout color="#4def93"}从查询或过程中无条件退出。 RETURN 的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。 RETURN 之后的语句是不执行的。{/callout}{alert type="success"}-- 示例一 CREATE PROCEDURE findjobs @nm sysname = NULL AS IF @nm IS NULL BEGIN PRINT 'You must give a user name' RETURN END ELSE BEGIN SELECT o.name, o.id, o.uid FROM sysobjects o INNER JOIN master..syslogins l ON o.uid = l.sid WHERE l.name = @nm END; -- 示例二 USE AdventureWorks2012; GO CREATE PROCEDURE checkstate @param VARCHAR(11) AS IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA' RETURN 1 ELSE RETURN 2; GO {/alert}THROW{callout color="#4da1ef"}引发异常，并将执行转移到 TRY...CATCH 构造的 CATCH 块。{/callout}{alert type="success"}-- 语法 THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ] -- error_number : 表示异常的常量或变量 error_number 为 int，并且必须大于或等于50000且小于或等于2147483647 -- message : 描述异常的字符串或变量。 message 为 nvarchar(2048) -- State : 在 0 到 255 之间的常量或变量，指示与消息关联的状态。 state 为 tinyint -- 示例一 THROW 51000, 'The record does not exist.', 1; -- 示例二 USE tempdb; GO CREATE TABLE dbo.TestRethrow ( ID INT PRIMARY KEY ); BEGIN TRY INSERT dbo.TestRethrow(ID) VALUES(1); -- Force error 2627, Violation of PRIMARY KEY constraint to be raised. INSERT dbo.TestRethrow(ID) VALUES(1); END TRY BEGIN CATCH PRINT 'In catch block.'; THROW; END CATCH; {/alert}TRY...CATCH{alert type="success"}-- 语法 BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH -- 示例 BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH; {/alert}WHILE{callout color="#4dbfef"}设置重复执行 SQL 语句或语句块的条件。 只要指定的条件为真，就重复执行语句。 可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。{/callout}{alert type="success"}-- 示例 USE AdventureWorks2012; GO WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 SELECT MAX(ListPrice) FROM Production.Product IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear'; -- 示例二 WHILE ( SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300 BEGIN UPDATE dbo.DimProduct SET ListPrice = ListPrice * 2; SELECT MAX ( ListPrice) FROM dbo.DimProduct IF ( SELECT MAX (ListPrice) FROM dbo.DimProduct) > $500 BREAK; END {/alert}

7. SQLServer : 其他函数(JSON函数、逻辑函数、聚合函数、排名函数、配置) JSON函数相关(SQLServer16及以上)ISJSON ：测试字符串是否包含有效JSON{alert type="success"}select ISJSON('{"a":1,"b":2}'); -- 1 : true 0：fasle{/alert}JSON_VALUE : 从字符串中提取标量值{alert type="success"}select json_value('{"a":1,"b":2,"c":{"cc":3}}','$.b'); -- 2 select json_value('{"a":1,"b":2,"c":{"cc":3}}','$.c.cc'); -- 3{/alert}JSON_QUERY : 从 JSON 字符串中提取对象或数组{callout color="#4fef4d"}如果值不是对象或者数组、会报错或者返回NULL{/callout}{alert type="success"}select JSON_QUERY('{"a":1,"b":2,"c":{"cc":3}}','$.c.cc'); -- Null select JSON_QUERY('{"a":1,"b":2,"c":{"cc":3}}','$.c'); -- {"cc":3}{/alert}JSON_MODIFY : 更新 JSON 字符串中属性的值，并返回已更新的 JSON 字符串{alert type="success"}-- 语法 JSON_MODIFY ( expression , path , newValue ) -- 示例 select JSON_MODIFY('{"a":1,"b":2,"c":{"cc":3}}','$.c','{dd:4}'); -- {"a":1,"b":2,"c":"{dd:4}"}{/alert}将JSON 集合转换为行集{callout color="#9aaaea"}如果必须基于 JSON 数据创建查询或报表，可以通过调用 OPENJSON 行集函数，轻松地将 JSON 数据转换为行与列 。{/callout}{alert type="info"}DECLARE @json NVARCHAR(MAX); SET @json = N'[ {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25}, {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"} ]'; SELECT * FROM OPENJSON(@json) WITH ( id INT 'strict $.id', firstName NVARCHAR(50) '$.info.name', lastName NVARCHAR(50) '$.info.surname', age INT, dateOfBirth DATETIME2 '$.dob' ); DECLARE @json NVARCHAR(MAX); SET @json = N'[ {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25}, {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"} ]'; SELECT id, firstName, lastName, age, dateOfBirth, skill FROM OPENJSON(@json) WITH ( id INT 'strict $.id', firstName NVARCHAR(50) '$.info.name', lastName NVARCHAR(50) '$.info.surname', age INT, dateOfBirth DATETIME2 '$.dob', skills NVARCHAR(MAX) '$.info.skills' AS JSON ) OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(8) '$');{/alert}将 SQL Server 数据转换为 JSON 或导出 JSON{alert type="info"}SELECT id, firstName AS "info.name", lastName AS "info.surname", age, dateOfBirth AS dob FROM People FOR JSON PATH;{/alert}OPENJSON{alert type="info"}在不提供结果的显式架构的情况下使用 OPENJSON 函数时（即，在 OPENJSON 之后不使用 WITH 子句），该函数将返回包含以下三列的表：输入对象中属性的名称（或输入数组中元素的索引）。属性或数组元素的值。类型（例如，字符串、数字、布尔值、数组或对象）。OPENJSON 以单独的行返回 JSON 对象的每个属性或数组的每个元素。{/alert}具有默认输出的 OPENJSON{alert type="info"}下面是使用具有默认架构（即不包含可选的 WITH 子句）的 OPENJSON 的快捷示例，该示例为 JSON 对象的每个属性返回一行。 {/alert}{alert type="info"}DECLARE @json NVARCHAR(MAX) SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; SELECT * FROM OPENJSON(@json);{/alert}具有显式结构的 OPENJSON 输出{alert type="info"}如果使用 OPENJSON 函数的 WITH 子句指定结果的架构，该函数返回的表只包含 WITH 子句中定义的列。 在可选的 WITH 子句中，指定一组输出列、列类型和每个输出值的 JSON 源属性的路径。 OPENJSON 循环访问 JSON 对象的数组，读取每一列的指定路径上的值，并将值转换为指定类型。{/alert}{alert type="info"}DECLARE @json NVARCHAR(MAX) SET @json = N'[ { "Order": { "Number":"SO43659", "Date":"2011-05-31T00:00:00" }, "AccountNumber":"AW29825", "Item": { "Price":2024.9940, "Quantity":1 } }, { "Order": { "Number":"SO43661", "Date":"2011-06-01T00:00:00" }, "AccountNumber":"AW73565", "Item": { "Price":2024.9940, "Quantity":3 } } ]' SELECT * FROM OPENJSON ( @json ) WITH ( Number varchar(200) '$.Order.Number' , Date datetime '$.Order.Date', Customer varchar(200) '$.AccountNumber', Quantity int '$.Item.Quantity' ) {/alert}返回对象的各个属性{alert type="info"}-- 原始 SELECT * FROM OPENJSON('{"name":"John","surname":"Doe","age":45}') -- 分析 SELECT * FROM OPENJSON('{"name":"John","surname":"Doe","age":45}') with ( name nvarchar(255), surname nvarchar(255), age nvarchar(255) ) {/alert}返回数组各元素{alert type="info"}SELECT * FROM OPENJSON('["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]') {/alert}将 JSON 转换成临时表{alert type="info"}-- 以下查询返回 info 对象的所有属性 DECLARE @json NVARCHAR(MAX) SET @json=N'{ "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }' SELECT * FROM OPENJSON(@json,N'lax $.info'){/alert}使用 WITH 子句设置输出格式{alert type="info"}DECLARE @json NVARCHAR(MAX) = N'{"someObject": {"someArray": [ {"k1": 11, "k2": null, "k3": "text"}, {"k1": 21, "k2": "text2", "k4": { "data": "text4" }}, {"k1": 31, "k2": 32}, {"k1": 41, "k2": null, "k4": { "data": false }} ] } }' SELECT * FROM OPENJSON(@json, N'lax $.someObject.someArray') WITH ( k1 int, k2 varchar(100), col3 varchar(6) N'$.k3', col4 varchar(10) N'lax $.k4.data', col5 nvarchar(MAX) N'lax $.k4' AS JSON, array_element nvarchar(MAX) N'$' AS JSON ) {/alert}使用 FOR JSON 将查询结果格式化为 JSON{alert type="info"}使用 FOR JSON PATH 来保持对 JSON 输出格式的完全控制。 你可以创建包装对象并嵌套复杂属性。若要根据 SELECT 语句的结构自动格式化 JSON 输出，请使用 FOR JSON AUTO。{/alert}{alert type="info"}SELECT name, surname FROM emp FOR JSON AUTO;{/alert}聚合函数AVG : 平均值计算{alert type="info"}-- 语法 AVG ( [ ALL | DISTINCT ] expression ) [ OVER ( [ partition_by_clause ] order_by_clause ) ]{/alert}搭配 GROUP BY 子句使用 SUM 和 AVG 函数{alert type="info"}SELECT TerritoryID, AVG(Bonus)as 'Average bonus', SUM(SalesYTD) as 'YTD sales' FROM Sales.SalesPerson GROUP BY TerritoryID; GO {/alert}带 DISTINCT 使用 AVG{callout color="#f0ad4e"}DISTINCT剔除字段中重复值{/callout}{alert type="info"}SELECT AVG(DISTINCT ListPrice) FROM Production.Product; SELECT AVG(ListPrice) FROM Production.Product; {/alert}使用 OVER 子句{callout color="#4def86"}{/callout}{alert type="success"}SELECT BusinessEntityID, TerritoryID ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS MovingAvg ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS CumulativeTotal FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY TerritoryID,SalesYear; {/alert}COUNT{alert type="info"}-- 语法 -- Aggregation Function Syntax COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } ) -- Analytic Function Syntax COUNT ( [ ALL ] { expression | * } ) OVER ( [ <partition_by_clause> ] ) {/alert}使用 COUNT 和 DISTINCT{alert type="success"}SELECT COUNT(DISTINCT Title) FROM HumanResources.Employee; {/alert}使用 COUNT(*){alert type="success"}SELECT COUNT(*) FROM HumanResources.Employee; {/alert}使用OVER子句{alert type="success"}SELECT DISTINCT Name , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept FROM HumanResources.EmployeePayHistory AS eph JOIN HumanResources.EmployeeDepartmentHistory AS edh ON eph.BusinessEntityID = edh.BusinessEntityID JOIN HumanResources.Department AS d ON d.DepartmentID = edh.DepartmentID WHERE edh.EndDate IS NULL ORDER BY Name; {/alert}MAX、MIN、SUM{alert type="success"}-- 语法 -- Aggregation Function Syntax MAX( [ ALL | DISTINCT ] expression ) -- Analytic Function Syntax MAX ([ ALL ] expression) OVER ( <partition_by_clause> [ <order_by_clause> ] ) {/alert}{alert type="info"}SELECT DISTINCT Name , MIN(Rate) OVER (PARTITION BY edh.DepartmentID) AS MinSalary , MAX(Rate) OVER (PARTITION BY edh.DepartmentID) AS MaxSalary , AVG(Rate) OVER (PARTITION BY edh.DepartmentID) AS AvgSalary ,COUNT(edh.BusinessEntityID) OVER (PARTITION BY edh.DepartmentID) AS EmployeesPerDept FROM HumanResources.EmployeePayHistory AS eph JOIN HumanResources.EmployeeDepartmentHistory AS edh ON eph.BusinessEntityID = edh.BusinessEntityID JOIN HumanResources.Department AS d ON d.DepartmentID = edh.DepartmentID WHERE edh.EndDate IS NULL ORDER BY Name; {/alert}配置{alert type="info"}配置说明@@DBTS当前数据库的当前 timestamp 数据类型的值@@LANGUAGE当前所用语言的名称@@MAX_CONNECTIONS允许同时进行的最大用户连接数@@MAX_PRECISION返回 decimal 和 numeric 数据类型所用的精度级别@@SERVERNAME运行 SQL Server 的本地服务器的名称@@SPID返回当前用户进程的会话 ID@@VERSION当前 SQL Server 安装的系统和生成信息{/alert}逻辑函数CHOOSE{callout color="#f0ad4e"}在 SQL Server 中从值列表返回指定索引处的项。{/callout}{alert type="success"}SELECT CHOOSE ( 3, 'Manager', 'Director', 'Developer', 'Tester' ) AS Result; SELECT Name, ModifiedDate, CHOOSE(MONTH(ModifiedDate),'Winter','Winter', 'Spring','Spring','Spring','Summer','Summer', 'Summer','Autumn','Autumn','Autumn','Winter') AS Quarter_Modified FROM SalesLT.ProductModel AS PM WHERE Name LIKE '%Frame%' ORDER BY ModifiedDate; {/alert}IIF{alert type="success"}-- 如果布尔表达式为 true，则返回 true_value；如果布尔表达式为 false 或未知，则返回 false_value IIF( boolean_expression, true_value, false_value ) DECLARE @P INT = NULL, @S INT = NULL; SELECT [Result] = IIF( 45 > 30, @P, @S );{/alert}排名函数{callout color="#4def6d"}排名函数为分区中的每一行返回一个排名值。根据所使用的函数，某些行可能会收到与其他行相同的值。排名函数是不确定的。{/callout}{alert type="success"}SELECT p.FirstName, p.LastName ,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number" ,RANK() OVER (ORDER BY a.PostalCode) AS Rank ,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank" ,NTILE(4) OVER (ORDER BY a.PostalCode) AS Quartile ,s.SalesYTD ,a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; FirstName LastName Row Number Rank Dense Rank Quartile SalesYTD PostalCode Michael Blythe 1 1 1 1 4557045.0459 98027 Linda Mitchell 2 1 1 1 5200475.2313 98027 Jillian Carson 3 1 1 1 3857163.6332 98027 Garrett Vargas 4 1 1 1 1764938.9859 98027 Tsvi Reiter 5 1 1 2 2811012.7151 98027 Shu Ito 6 6 2 2 3018725.4858 98055 José Saraiva 7 6 2 2 3189356.2465 98055 David Campbell 8 6 2 3 3587378.4257 98055 Tete Mensa-Annan 9 6 2 3 1931620.1835 98055 Lynn Tsoflias 10 6 2 3 1758385.926 98055 Rachel Valdez 11 6 2 4 2241204.0424 98055 Jae Pak 12 6 2 4 5015682.3752 98055 Ranjit Varkey Chudukatil 13 6 2 4 3827950.238 98055 {/alert}DENSE_RANK{callout color="#f788c3"}此函数返回结果集分区中每行的排名，排名值没有间断。 特定行的排名等于该特定行之前不同排名值的数量加一。{/callout}{alert type="success"}-- 语法 DENSE_RANK ( ) OVER ( [ <partition_by_clause> ] < order_by_clause > ) SELECT i.ProductID, p.Name, i.LocationID, i.Quantity ,DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity DESC) AS Rank FROM Production.ProductInventory AS i INNER JOIN Production.Product AS p ON i.ProductID = p.ProductID WHERE i.LocationID BETWEEN 3 AND 4 ORDER BY i.LocationID; {/alert}RANK{callout color="#f0ad4e"}返回结果集分区内每一行的排名。行的排名是相关行之前的排名数加1{/callout}{alert type="success"}RANK ( ) OVER ( [ partition_by_clause ] order_by_clause ) {/alert}NTILE{callout color="#4defdd"}将有序分区中的行分发到指定数目的组中。 各个组有编号，编号从一开始。 对于每一个行，NTILE 将返回此行所属的组的编号。{/callout}{alert type="success"}NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > ) SELECT p.FirstName, p.LastName ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS Quartile ,CONVERT(NVARCHAR(20),s.SalesYTD,1) AS SalesYTD , a.PostalCode FROM Sales.SalesPerson AS s INNER JOIN Person.Person AS p ON s.BusinessEntityID = p.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = p.BusinessEntityID WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0; {/alert}ROW_NUMBER{callout color="#4defdd"}对结果集的输出进行编号。更具体地说，返回结果集分区内行的序号，每个分区中的第一行从 1 开始。ROW_NUMBER并且RANK是相似的。ROW_NUMBER按顺序编号所有行（例如 1、2、3、4、5）。RANK为平局提供相同的数值（例如 1、2、2、4、5）。{/callout}{alert type="info"}ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause ) {/alert}

3. FOR XML (SQL Server) : SELECT 查询将结果作为行集返回 环境要求{alert type="info"} MicroSoft SqlServer 文档 所有版本 SQL Server版本均支持此语法 {/alert}基本初识{alert type="success"}在 FOR XML 子句中，有 RAW、AUTO、EXPLICIT、PATH 四种模式。RAW 模式：　　RAW 模式在 SELECT 语句返回的行集中为每个行生成一 <个行> 元素。 可以通过编写嵌套 FOR XML 查询来生成 XML 层次结构。AUTO 模式：　　AUTO 模式将基于指定 SELECT 语句的方式来使用试探性方法在 XML 结果中生成嵌套。 您对生成的 XML 的形状具有最低限度的控制能力。 除了 AUTO 模式的试探性方法生成的 XML 形状之外，还可以编写 FOR XML 查询来生成 XML 层次结构。EXPLICIT模式：　　EXPLICIT 模式允许对 XML 的形状进行更多控制。 您可以随意混合属性和元素来确定 XML 的形状。PATH模式：　　PATH 模式与嵌套 FOR XML 查询功能一起以较简单的方式提供了 EXPLICIT 模式的灵活性。{/alert}测试数据库：{card-default label="创建数据库表" width="100%"}CREATE TABLE [dbo].[userTable]( [id] [nvarchar](50) NULL, [encode] [nvarchar](50) NULL, [name] [nvarchar](50) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[userTable] ADD CONSTRAINT [DF_user_id] DEFAULT (newid()) FOR [id] GO {/card-default} {card-default label="插入测试数据" width="100%"}INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'1A074FE5-BAB3-4B71-932D-393387E94B5F', N'001', N'张三') GO INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'F75F5FA1-D8A1-4076-B840-99941DB9038F', N'002', N'李四') GO INSERT [dbo].[userTable] ([id], [encode], [name]) VALUES (N'FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4', N'003', N'王五') GO INSERT [dbo].[userTable] ([id],[encode], [name]) VALUES (N'79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A', N'004',null) {/card-default}{card-default label="测试表视图" width=""}idencodename1A074FE5-BAB3-4B71-932D-393387E94B5F001张三F75F5FA1-D8A1-4076-B840-99941DB9038F002李四FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4003王五79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A004NULL{/card-default}使用示例：{callout color="#bff990"}将查询结果作为行集返回，即只有一个查询行，此行只有一个查询列。多个指令可使用 逗号 分割开。{/callout}RAW模式示例一 : 基本使用{card-default label="SQL语句" width="100%"}select * from userTable for xml raw ;{/card-default}{card-default label="执行结果" width="100%"}<!--普通使用--> <row id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" /> <row id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" /> <row id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" /> <row id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />{/card-default}示例二 : ELEMENTS 指令{card-default label="SQL语句" width="100%"}select * from userTable for xml raw,ELEMENTS ;{/card-default} {card-default label="执行结果" width="100%"}<!--ELEMENTS指令：检索以元素为中心的 XML--> <row> <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id> <encode>001</encode> <name>张三</name> </row> <row> <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id> <encode>002</encode> <name>李四</name> </row> <row> <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id> <encode>003</encode> <name>王五</name> </row> <row> <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id> <encode>004</encode> </row>{/card-default}示例三 : XSINIL 添加指令 ELEMENTS{card-default label="SQL语句" width="100%"}select * from userTable for xml raw,ELEMENTS XSINIL ;{/card-default}{card-default label="执行结果" width="100%"}<!--XSINIL 添加指令 ELEMENTS：对为 Null 值的数据生成XML--> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id> <encode>001</encode> <name>张三</name> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id> <encode>002</encode> <name>李四</name> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id> <encode>003</encode> <name>王五</name> </row> <row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id> <encode>004</encode> <name xsi:nil="true" /> </row>{/card-default}示例四 : XMLDATA 指令{card-default label="SQL语句" width="100%"}select * from userTable for xml raw,XMLDATA ;{/card-default}{card-default label="执行结果" width="100%"}<!-- 返回描述文档结构的 XML-DATA 架构 --> <Schema name="Schema1" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes"> <ElementType name="row" content="empty" model="closed"> <AttributeType name="id" dt:type="string" /> <AttributeType name="encode" dt:type="string" /> <AttributeType name="name" dt:type="string" /> <attribute type="id" /> <attribute type="encode" /> <attribute type="name" /> </ElementType> </Schema> <row xmlns="x-schema:#Schema1" id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" /> <row xmlns="x-schema:#Schema1" id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" /> <row xmlns="x-schema:#Schema1" id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" /> <row xmlns="x-schema:#Schema1" id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />{/card-default}其他{alert type="info"}FOR XML RAW ('xx'): 重命名 <行> 元素。ROOT 指令： 为 FOR XML 生成的 XML 指定根元素。比如：ROOT('MyRoot');XMLSCHEMA 指令： 返回 XSD 架构 。比如：可以使用 XMLSCHEMA ('urn:example.com') 格式指定命名空间 。BINARY BASE64 指令： 返回在 varbinary(max)、binary 类型列中存储的 以 base64 编码格式返回二进制数据。{/alert}AUTO 模式示例一:{card-default label="SQL语句" width="100%"}select * from userTable x for xml AUTO;{/card-default}{card-default label="执行结果" width="100%"}<!-- 可以为表设置别名 同时结合上述指令 --> <userTable id="1A074FE5-BAB3-4B71-932D-393387E94B5F" encode="001" name="张三" /> <userTable id="F75F5FA1-D8A1-4076-B840-99941DB9038F" encode="002" name="李四" /> <userTable id="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" encode="003" name="王五" /> <userTable id="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" encode="004" />{/card-default}示例二:{callout color="#f0ad4e"}　　先指定 OrderHeader 表中的列，再指定 Cust 表中的列， 因此，将创建第一个 元素，然后将 子元素添加到其中。{/callout}{card-default label="SQL语句" width="100%"}elect OrderHeader.CustomerID, OrderHeader.SalesOrderID, OrderHeader.Status, Cust.CustomerID, Cust.CustomerType from Sales.Customer Cust, Sales.SalesOrderHeader OrderHeader where Cust.CustomerID = OrderHeader.CustomerID for xml auto; {/card-default}{card-default label="执行结果" width="100%"}<!-- 可结合 ELEMENTS 等其他指令--> <OrderHeader CustomerID="1" SalesOrderID="43860" Status="5"> <Cust CustomerID="1" CustomerType="S" /> </OrderHeader> ...{/card-default}PATH 模式示例一{card-default label="SQL语句" width="100%"}select * from userTable for xml PATH {/card-default}{card-default label="执行结果" width="100%"}<row> <id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id> <encode>001</encode> <name>张三</name> </row> <row> <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id> <encode>002</encode> <name>李四</name> </row> <row> <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id> <encode>003</encode> <name>王五</name> </row> <row> <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id> <encode>004</encode> </row>{/card-default}示例二{card-default label="SQL语句" width="100%"}select * from userTable for xml PATH('') ;{/card-default}{card-default label="执行结果" width="100%"}<id>1A074FE5-BAB3-4B71-932D-393387E94B5F</id> <encode>001</encode> <name>张三</name> <id>F75F5FA1-D8A1-4076-B840-99941DB9038F</id> <encode>002</encode> <name>李四</name> <id>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</id> <encode>003</encode> <name>王五</name> <id>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</id> <encode>004</encode>{/card-default}示例三{callout color="#58d336"}如果列名称以符号 (@) 开头，并且不包含斜杠标记 (/)，则会创建具有相应列值的元素的属性 row在对应的语句中，可根据不同模式对表名、列名进行别名的设置而得到相应得到XML结构{/callout}{card-default label="SQL语句" width="100%"}select id as '@part',encode as '@code',name from userTable for xml PATH{/card-default}{card-default label="执行结果" width="100%"}<row part="1A074FE5-BAB3-4B71-932D-393387E94B5F" code="001"> <name>张三</name> </row> <row part="F75F5FA1-D8A1-4076-B840-99941DB9038F" code="002"> <name>李四</name> </row> <row part="FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4" code="003"> <name>王五</name> </row> <row part="79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A" code="004" />{/card-default}示例四{callout color="#f4d19f"}如果列名不以符号 () @ 开头，但包含斜杠标记 (/) ，则列名表示 XML 层次结构。 {/callout}{card-default label="SQL语句" width="100%"}select ID as 'ID1/ID2',ENCODE,NAME from userTable for xml PATH{/card-default}{card-default label="执行结果" width="100%"}<row> <ID1> <ID2>1A074FE5-BAB3-4B71-932D-393387E94B5F</ID2> </ID1> <ENCODE>001</ENCODE> <NAME>张三</NAME> </row> <row> <ID1> <ID2>F75F5FA1-D8A1-4076-B840-99941DB9038F</ID2> </ID1> <ENCODE>002</ENCODE> <NAME>李四</NAME> </row> <row> <ID1> <ID2>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4</ID2> </ID1> <ENCODE>003</ENCODE> <NAME>王五</NAME> </row> <row> <ID1> <ID2>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A</ID2> </ID1> <ENCODE>004</ENCODE> </row>{/card-default}示例五{callout color="#f2eade"}如果指定的列名是通配符 (*) ，则插入该列的内容就好像没有指定列名一样。 如果此列不是xml 类型的列，则此列的内容将作为文本节点插入.{/callout}{card-default label="SQL语句" width="100%"}select ID as '*',ENCODE,NAME from userTable for xml PATH {/card-default}{card-default label="执行结果" width="100%"}<row>1A074FE5-BAB3-4B71-932D-393387E94B5F<ENCODE>001</ENCODE><NAME>张三</NAME></row> <row>F75F5FA1-D8A1-4076-B840-99941DB9038F<ENCODE>002</ENCODE><NAME>李四</NAME></row> <row>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4<ENCODE>003</ENCODE><NAME>王五</NAME></row> <row>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A<ENCODE>004</ENCODE></row>{/card-default}示例六{callout color="#c4c0ba"}如果列名是某个 XPath 节点测试，将如下表所示映射该列的内容。 如果列名是某个 XPath 节点测试，则该列的内容将映射到相应的节点。 如果该列的 SQL 类型是 xml，将返回一个错误。{/callout}列名行为text()对于名为 text() 的列，该列中的字符串值将被添加为文本节点。comment()对于名为 comment() 的列，该列中的字符串值将被添加为 XML 注释。node()对于节点名称 () 的列，结果与列名称是通配符 () * 时的结果相同。处理指令（名称）如果列名为处理指令，该列中的字符串值将被添加为此处理指令目标名称的 PI 值。{card-default label="SQL语句" width="100%"}select ID as 'text()',ENCODE as 'comment()',NAME as 'node()' from userTable for xml PATH {/card-default}{card-default label="执行结果" width="100%"}<row>1A074FE5-BAB3-4B71-932D-393387E94B5F<!--001-->张三</row> <row>F75F5FA1-D8A1-4076-B840-99941DB9038F<!--002-->李四</row> <row>FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4<!--003-->王五</row> <row>79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A<!--004--></row>{/card-default}示例七{card-default label="SQL语句" width="100%"}select ','+ID from userTable for xml PATH(''){/card-default}{card-default label="执行结果" width="100%"},1A074FE5-BAB3-4B71-932D-393387E94B5F,F75F5FA1-D8A1-4076-B840-99941DB9038F,FF611E9B-2D28-4C53-A4DE-FE67A16D4FD4,79BB7B1A-CAC9-4D2E-A0CE-56C91F8FF76A{/card-default}EXPLICIT 模式{callout color="#37ae9b"}具体可参阅微软官方文档{/callout}

2. 分组聚合函数：ROW_NUMBER() OVER (PARTITION BY Column ORDER BY COLUMN {alert type="success"}分组聚合函数可以对数据分组并排序以按分组增加一个自动递增的列以得到理想的数据。SQLServer版本至少2005版语法:ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY COLUMN2)Column1: 分组列Column2：排序列演示：1.创建如下表Table_1结构并插入数据：idkysl1kysl22D0357A3-8CCF-442A-B0C1-35DFE2A881CBaa1AB022164-0C61-4321-8E36-3A5A3E35EA4Ecc3314C03C5-D79C-4EA0-82CD-25337935E0A0bb2BD9C0338-8933-4423-81DB-A0AEE05D03F0aa240718F52-F815-4C61-968E-2BAEE248CA81cc416088343-FB69-4917-9677-DAEA3ABB8A09bb57EE903A5-2CF9-4BB5-8B66-33E6AEFEF3FDaa30AF9501D-C4A9-4CC0-B753-CABDDB90B783cc5822DAA22-786C-4B67-827E-A5BB10B60845bb42.执行SQL：select *,ROW_NUMBER() OVER (PARTITION BY kysl ORDER BY KYSL2 )ws from [Table_1] 结果如下：idkysl1kysl2n2D0357A3-8CCF-442A-B0C1-35DFE2A881CBaa11BD9C0338-8933-4423-81DB-A0AEE05D03F0aa227EE903A5-2CF9-4BB5-8B66-33E6AEFEF3FDaa33314C03C5-D79C-4EA0-82CD-25337935E0A0bb21822DAA22-786C-4B67-827E-A5BB10B60845bb4216088343-FB69-4917-9677-DAEA3ABB8A09bb53AB022164-0C61-4321-8E36-3A5A3E35EA4Ecc3140718F52-F815-4C61-968E-2BAEE248CA81cc420AF9501D-C4A9-4CC0-B753-CABDDB90B783cc53{/alert}