萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL 進階技巧:實用高效的查詢語句集錦(補充版)

更多實戰 SQL 模式:分析函數、遞迴 CTE 與效能模式

延續前文(ID 46790),補充進階分析函數、階層查詢、間隙與島嶼問題等高頻進階模式。

1. 窗口函數:ROW_NUMBER、RANK、LAG/LEAD

分組取每組最新一筆(Top-N Per Group)

WITH Ranked AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY OrderDate DESC) AS rn
    FROM Orders
)
SELECT * FROM Ranked WHERE rn = 1;

計算同比/環比增長率

SELECT 
    YearMonth,
    SalesAmount,
    LAG(SalesAmount, 1) OVER (ORDER BY YearMonth) AS PrevMonth,
    (SalesAmount - LAG(SalesAmount, 1) OVER (ORDER BY YearMonth)) * 1.0 / 
    NULLIF(LAG(SalesAmount, 1) OVER (ORDER BY YearMonth), 0) AS MoM_Growth
FROM MonthlySales;

2. 遞迴 CTE:階層結構遍歷

組織架構全路徑

WITH OrgTree AS (
    SELECT EmployeeID, ManagerID, Name, CAST(Name AS NVARCHAR(MAX)) AS Path, 0 AS Level
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name, 
           CAST(o.Path + ' > ' + e.Name AS NVARCHAR(MAX)), o.Level + 1
    FROM Employees e
    JOIN OrgTree o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgTree ORDER BY Path;

3. 間隙與島嶼:連續區間識別

找出缺號區間(如發票號碼斷號)

DECLARE @Cols NVARCHAR(MAX), @Sql NVARCHAR(MAX);
SELECT @Cols = STRING_AGG(QUOTENAME(YearMonth), ',') 
FROM (SELECT DISTINCT YearMonth FROM Sales) t;

SET @Sql = N'
SELECT ProductName, ' + @Cols + N'
FROM (
    SELECT ProductName, YearMonth, Amount
    FROM Sales
) src
PIVOT (SUM(Amount) FOR YearMonth IN (' + @Cols + N')) pvt';

EXEC sp_executesql @Sql;

5. 高效分頁查詢 (SQL Server 2012+)

SELECT * FROM LargeTable
ORDER BY Id
OFFSET 1000 ROWS FETCH NEXT 20 ROWS ONLY;

6. JSON 資料處理 (SQL Server 2016+)

-- 查詢 JSON 欄位
SELECT JSON_VALUE(Data, '$.customer.name') AS CustomerName
FROM Orders
WHERE JSON_VALUE(Data, '$.status') = 'Paid';

-- 修改 JSON
UPDATE Orders
SET Data = JSON_MODIFY(Data, '$.status', 'Shipped')
WHERE OrderID = 123;

7. 暫時表 vs 表變數 選擇指南

特性暫時表 (#temp)表變數 (@table)
統計資訊有,優化器可估算無,假設 1 行
索引/約束支援完整僅主鍵/唯一
交易回滾受影響不受影響
並行度可觸發並行通常串行
適用場景大資料量、複雜查詢小資料集、需跨交易保留
copyright © 萬盛學電腦網 all rights reserved