advanced-patterns

高级 T-SQL 模式

用于复杂 SQL Server 场景的高级技术。

快速参考

模式选择指南

任务	模式
层次结构数据	递归 CTE
每组前 N 条	ROW_NUMBER + CTE
相关子查询的替代方案	CROSS/OUTER APPLY
插入或更新（Upsert）	MERGE
捕获已修改的行	OUTPUT 子句
历史数据跟踪	时态表
高吞吐量 OLTP	内存中 OLTP
多个聚合级别	ROLLUP/CUBE/GROUPING SETS

公用表表达式（CTE）

基本 CTE

WITH RecentOrders AS (
    SELECT CustomerID, OrderDate, Amount
    FROM Orders
    WHERE OrderDate >= DATEADD(month, -3, GETDATE())
)
SELECT c.CustomerName, r.Amount
FROM Customers c
JOIN RecentOrders r ON c.CustomerID = r.CustomerID;

多个 CTE

WITH
Sales AS (
    SELECT ProductID, SUM(Amount) AS TotalSales FROM Orders GROUP BY ProductID
),
Inventory AS (
    SELECT ProductID, SUM(Quantity) AS TotalInventory FROM Stock GROUP BY ProductID
)
SELECT p.ProductName, s.TotalSales, i.TotalInventory
FROM Products p
LEFT JOIN Sales s ON p.ProductID = s.ProductID
LEFT JOIN Inventory i ON p.ProductID = i.ProductID;

递归 CTE（层次结构）

WITH OrgChart AS (
    -- Anchor: Top-level (no manager)
    SELECT EmployeeID, Name, ManagerID, 0 AS Level,
           CAST(Name AS VARCHAR(1000)) AS Path
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: Subordinates
    SELECT e.EmployeeID, e.Name, e.ManagerID, oc.Level + 1,
           CAST(oc.Path + ' > ' + e.Name AS VARCHAR(1000))
    FROM Employees e
    JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart
OPTION (MAXRECURSION 100);  -- Default is 100, max is 32767

用于删除重复项的 CTE

WITH Duplicates AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY Email
               ORDER BY CreatedDate DESC
           ) AS RowNum
    FROM Users
)
DELETE FROM Duplicates WHERE RowNum > 1;

APPLY 运算符

CROSS APPLY（内连接行为）

-- Top 3 orders per customer
SELECT c.CustomerID, c.Name, o.OrderID, o.Amount
FROM Customers c
CROSS APPLY (
    SELECT TOP 3 OrderID, Amount
    FROM Orders
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;

OUTER APPLY（左连接行为）

-- Last order per customer (including customers with no orders)
SELECT c.CustomerID, c.Name, o.LastOrderDate, o.LastOrderAmount
FROM Customers c
OUTER APPLY (
    SELECT TOP 1 OrderDate AS LastOrderDate, Amount AS LastOrderAmount
    FROM Orders
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;

与表值函数一起使用 APPLY

-- Call function for each row
SELECT c.CustomerID, f.MonthlyTotal, f.OrderCount
FROM Customers c
CROSS APPLY dbo.GetCustomerMonthlyStats(c.CustomerID) f;

使用 APPLY 取消列透视

-- Transform columns to rows
SELECT ID, AttributeName, AttributeValue
FROM Products
CROSS APPLY (
    VALUES
        ('Color', Color),
        ('Size', Size),
        ('Weight', CAST(Weight AS VARCHAR))
) AS Unpivoted(AttributeName, AttributeValue)
WHERE AttributeValue IS NOT NULL;

MERGE 语句

基本插入或更新

MERGE INTO TargetTable AS t
USING SourceTable AS s
ON t.ID = s.ID
WHEN MATCHED THEN
    UPDATE SET t.Name = s.Name, t.Value = s.Value, t.UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Value, CreatedAt)
    VALUES (s.ID, s.Name, s.Value, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.*, deleted.*;

带条件的 MERGE

MERGE INTO Products AS t
USING StagingProducts AS s
ON t.ProductID = s.ProductID
WHEN MATCHED AND s.Price <> t.Price THEN
    UPDATE SET t.Price = s.Price, t.LastModified = GETDATE()
WHEN MATCHED AND s.Discontinued = 1 THEN
    DELETE
WHEN NOT MATCHED THEN
    INSERT (ProductID, Name, Price) VALUES (s.ProductID, s.Name, s.Price);

OUTPUT 子句

捕获插入的行

DECLARE @InsertedRows TABLE (ID INT, Name VARCHAR(100));

INSERT INTO Customers (Name, Email)
OUTPUT inserted.CustomerID, inserted.Name INTO @InsertedRows
VALUES ('John', '[email protected]'), ('Jane', '[email protected]');

SELECT * FROM @InsertedRows;

捕获更新的行（更新前和更新后）

UPDATE Products
SET Price = Price * 1.1
OUTPUT deleted.ProductID, deleted.Price AS OldPrice, inserted.Price AS NewPrice
WHERE Category = 'Electronics';

捕获删除的行

DELETE FROM ExpiredOrders
OUTPUT deleted.*
INTO OrderArchive
WHERE ExpiryDate < DATEADD(year, -1, GETDATE());

高级分组

ROLLUP（分层小计）

SELECT
    COALESCE(Region, 'Total') AS Region,
    COALESCE(Product, 'All Products') AS Product,
    SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY ROLLUP (Region, Product);
-- Groups: (Region, Product), (Region), ()

CUBE（所有组合）

SELECT Region, Product, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY CUBE (Region, Product);
-- Groups: (Region, Product), (Region), (Product), ()

GROUPING SETS（自定义组合）

SELECT Region, Product, Year, SUM(Sales)
FROM SalesData
GROUP BY GROUPING SETS (
    (Region, Product),
    (Region, Year),
    (Product),
    ()
);

识别分组级别

SELECT
    CASE WHEN GROUPING(Region) = 1 THEN 'All' ELSE Region END AS Region,
    CASE WHEN GROUPING(Product) = 1 THEN 'All' ELSE Product END AS Product,
    SUM(Sales) AS TotalSales,
    GROUPING_ID(Region, Product) AS GroupLevel
    -- GroupLevel: 0 = both, 1 = Product rolled up, 2 = Region rolled up, 3 = both
FROM SalesData
GROUP BY ROLLUP (Region, Product);

时态表（SQL 2016+）

创建系统版本控制表

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Price DECIMAL(18,2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));

查询历史数据

-- Point in time
SELECT * FROM Products
FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00';

-- Time range
SELECT * FROM Products
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-06-30';

-- All history
SELECT * FROM Products
FOR SYSTEM_TIME ALL;

内存中 OLTP

创建内存优化表

-- First add filegroup
ALTER DATABASE YourDB
ADD FILEGROUP MemOptFG CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE YourDB
ADD FILE (NAME = 'MemOptFile', FILENAME = 'C:\Data\MemOpt') TO FILEGROUP MemOptFG;

-- Create table
CREATE TABLE OrdersMemOpt (
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    CustomerID INT NOT NULL INDEX IX_Customer NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    OrderDate DATETIME2 NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    INDEX IX_Date NONCLUSTERED (OrderDate)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

本机编译过程

CREATE PROCEDURE InsertOrderFast
    @CustomerID INT,
    @Amount DECIMAL(18,2)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    INSERT INTO dbo.OrdersMemOpt (OrderID, CustomerID, OrderDate, Amount)
    VALUES (NEXT VALUE FOR dbo.OrderSeq, @CustomerID, SYSDATETIME(), @Amount);
END;

表值构造器

将 VALUES 用作表

SELECT * FROM (
    VALUES
        (1, 'Apple', 1.50),
        (2, 'Banana', 0.75),
        (3, 'Orange', 2.00)
) AS Products(ID, Name, Price);

在 MERGE 中使用

MERGE INTO Products AS t
USING (VALUES
    (1, 'Apple', 1.60),
    (2, 'Banana', 0.80)
) AS s(ID, Name, Price)
ON t.ID = s.ID
WHEN MATCHED THEN UPDATE SET Price = s.Price
WHEN NOT MATCHED THEN INSERT VALUES (s.ID, s.Name, s.Price);

序列

创建和使用序列

CREATE SEQUENCE OrderSeq
    AS INT START WITH 1 INCREMENT BY 1;

-- Get next value
SELECT NEXT VALUE FOR OrderSeq;

-- Use in INSERT
INSERT INTO Orders (OrderID, CustomerID)
VALUES (NEXT VALUE FOR OrderSeq, @CustomerID);

-- Use as default
ALTER TABLE Orders
ADD CONSTRAINT DF_OrderID DEFAULT NEXT VALUE FOR OrderSeq FOR OrderID;