一些有用的T-SQL功能点

2024-07-27

不同的SQL引擎在一定程度上支持SQL标准之外，还有一些自己的特有语法，一般称为SQL方言，本文主要记录我觉得比较有用的“冷门”SQL Server方言。如果在实现某些特殊功能时只用最基础的功能点，代码将会显示非常冗长和繁琐，下面介绍的这些语法特性，可以让开发者用更简洁的代码实现某些特定功能。

在UPDATE语句中对变量赋值 ( update variable )

应用案例1 - 从表中获取最新序列号

低于SQL Server 2016的版本不支持Sequence类型，如果想从数据库中获取全局自增序列号，通常的做法是将它们存在一张表中，每次Update之后获取，典型代码如下：

准备数据：

USE TestDB
GO
DROP TABLE IF EXISTS dbo.fact_key;
CREATE TABLE dbo.fact_key(key_type VARCHAR(100) PRIMARY KEY, key_value int );
GO
INSERT INTO dbo.fact_key(key_type,key_value) VALUES ('Type-1',1), ('Type-2',1);
GO

如果我想要获取Type-1的下个序列号，常规获取方法是先Update再Select：

-- common method - two separated update and select
DECLARE @type VARCHAR(100) = 'Type-1', @next_key_value int;
UPDATE dbo.fact_key SET key_value = key_value + 1 WHERE key_type = @type;
SELECT @next_key_value = key_value FROM dbo.fact_key WHERE key_type = @type;
SELECT @next_key_value;

其实可以在同一个Update语句中直接获取更新之后的序列号：

-- new method - update variable
DECLARE @type VARCHAR(100) = 'Type-1', @next_key_value int;
UPDATE dbo.fact_key SET @next_key_value = key_value = key_value + 1 WHERE key_type = @type;
SELECT @next_key_value;

应用案例2 - running total

running total通常用窗口函数sum over(order by xxx)或自连接等方法实现，其实还有一种特别取巧但不推荐在生产环境中使用的方法，要用到update variable语法。举这个例子只是为了说明这个语法的用法，本人也并不推荐在项目的生产环境中用它来实现running total。

-- prapare data
DROP TABLE IF EXISTS dbo.test_sum_total;
CREATE TABLE dbo.test_sum_total(xvalue INT,running_total INT);
INSERT INTO dbo.test_sum_total(xvalue)
    SELECT TOP (100) 1
    FROM sys.all_columns;

-- update running total
DECLARE @total INT = 0;
UPDATE dbo.test_sum_total SET @total = running_total = @total + xvalue;

注：结果的正确性非常依赖具体的物理执行计划，和我另外一个笔记中提到的一个语法点类似T-SQL聚合拼接或求和，不可靠（SELECT @x = @x + …）

基于子查询的数据更新 ( update/delete/merge based on sub-query )

这个功能点非常适合在update/delete/merge之前先用Select语句将更新前后的数据展示出来，检查之后直接将select语句作为子查询进行更新。支持以子查询中的窗口函数的结果为条件进行过滤，典型应用场景为数据去重：

-- prepare data
DROP TABLE IF EXISTS dbo.test_remove_duplicate;
CREATE TABLE dbo.test_remove_duplicate(xvalue VARCHAR(200));
INSERT INTO  dbo.test_remove_duplicate(xvalue) VALUES ('a'),('a'),('a'),('b'),('b');

-- delete duplicated data
WITH cte AS (
    SELECT xvalue,ROW_NUMBER() OVER(PARTITION BY xvalue ORDER BY (SELECT NULL)) AS rn 
    FROM dbo.test_remove_duplicate
 )
DELETE cte WHERE rn > 1;

APPLY 操作符

cross apply以及相关的outer apply是T-SQL支持的强大功能，用它写的query一般用传统的join也可以实现，但在某些应用场景下across apply的写法可能会更好理解一些。 (row_set) cross apply (sub_query_or_table_function)可理解为：对输入集合中的每一行，进行子查询或表函数查询（可引用输入集合中的列），然后将结果集进行合并。类似于以下伪代码：

result_set = []
foreach row in row_set:
    result_set.add(sub_query_or_table_function(row))

其它数据库后来也支持这个功能，比如oracle为了兼容C#的LINQ，加入了对cross apply和outer apply的支持，Mysql，PgSQL，Spark SQL，DuckDB等有个LATERAL操作符也支持类似的处理逻辑。

应用举例1 - top N per group

假如有表:

sales.customers (custid int primary key, custname varchar(50), city varchar(20))
sales.orders (orderid int primary key, custid int, orderdate date, shipname varchar(40))

现要获取每个customer最新的一笔订单情况，除了用窗口函数之外，也可以用cross apply

SELECT a.custid, a.custname, b.orderid, b.orderdate, b.empid
FROM Sales.Customers a
CROSS apply (
    SELECT TOP (1) orderid, orderdate, empid
    FROM Sales.Orders
    WHERE custid = a.custid 
    ORDER BY orderdate DESC, orderid DESC
) b

应用举例2 - unpivot(列转行/逆透视)

见另一篇笔记 SQL Server用Cross Apply、Cross Join实现列转行

应用举例3 - reuse column aliases(列别名复用)

T-SQL无法像duckdb一样原生支持列别名复用，如果SQL的select列表中有一个长表达式A，另外一个表达式B需要引用A的结果，甚至是case when分支中的多次引用，通常做法是将A表达式copy后作为B表达式的一部分，或者通过嵌套子查询的方式去复用表达A的结果，这样的代码会看上去会非常冗长和晦涩。用cross apply可以让代码列更简洁一些:

VALUE 形式
FROM T1 APPLY ( VALUES(<expressions_based_on_columns_in_T1>) ) AS A(<aliases>)
SELECT 形式
FROM T1 APPLY ( SELECT <expressions_based_on_columns_in_T1> ) AS A(<aliases>)

CREATE TABLE t1 (c1 INT, c2 INT);
INSERT INTO t1 VALUES (0, 1), (1, 2);

SELECT c1, c2, a, b, c
FROM t1
CROSS APPLY (SELECT c1 + c2) as v1(a)
CROSS APPLY (SELECT c1 - c2) as v2(b)
CROSS APPLY (SELECT a * b) as v3(c) -- reuse alias a and b

DML Output子句

被delete/update/insert/merge语句删除或新增的记录可通过output关键字输出，通过inserted和deleted这两个固定的表别名分别选出新增和删除的记录。非常适合审计、归档等应用场景。merge配合output子句还可以很方便地实现数仓维表的SCD功能。

Temp SP - 临时存储过程

是的，SQL Server除了支持临时表，还支持临时存储过程，方便在权限受限无法新建常规SP时用临时SP实现代码复用。