延續前文(ID 46790),補充進階分析函數、階層查詢、間隙與島嶼問題等高頻進階模式。
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;
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;
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;
SELECT * FROM LargeTable ORDER BY Id OFFSET 1000 ROWS FETCH NEXT 20 ROWS ONLY;
-- 查詢 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;
| 特性 | 暫時表 (#temp) | 表變數 (@table) |
|---|---|---|
| 統計資訊 | 有,優化器可估算 | 無,假設 1 行 |
| 索引/約束 | 支援完整 | 僅主鍵/唯一 |
| 交易回滾 | 受影響 | 不受影響 |
| 並行度 | 可觸發並行 | 通常串行 |
| 適用場景 | 大資料量、複雜查詢 | 小資料集、需跨交易保留 |