在 MS SQL Server 中，WITH Deviation AS (...) 這種寫法屬于 CTE（Common Table Expression，公共表表達(dá)式）。
CTE 并不只有你看到的這一種“形式”，而是可以按 用途和能力 分成幾類。下面我系統(tǒng)地給你梳理一下 ??
（偏工程實(shí)戰(zhàn) + 易理解，適合你平時(shí)寫測(cè)試/分析 SQL）

一、CTE 的本質(zhì)（一句話理解）

CTE = 給一段子查詢起一個(gè)“臨時(shí)名字”，只在當(dāng)前 SQL 語句中有效

WITH CTE名 AS (
    SELECT ...
)
SELECT * FROM CTE名;

• 不是表
• 不是視圖
• 不會(huì)落盤
• 作用域只在 當(dāng)前語句

二、CTE 的幾種“常見類型 / 用法分類”

SQL 語法只有一種 WITH，但“用途”不同

1?? 普通（非遞歸）CTE —— 最常見 ?

?? 用來替代復(fù)雜子查詢，提高可讀性

示例

WITH Deviation AS (
    SELECT
        SN,
        Power - AvgPower AS Dev
    FROM TestData
)
SELECT *
FROM Deviation
WHERE ABS(Dev) > 1;

使用場(chǎng)景

• 復(fù)雜 SELECT
• 多層子查詢
• 臨時(shí)計(jì)算中間結(jié)果

?? 這是你現(xiàn)在用的這一類

2?? 多 CTE（鏈?zhǔn)?CTE）—— 一個(gè)接一個(gè)

?? 一個(gè) CTE 依賴另一個(gè) CTE

示例

WITH RawData AS (
    SELECT SN, Power
    FROM TestData
),
Deviation AS (
    SELECT
        SN,
        Power - AVG(Power) OVER() AS Dev
    FROM RawData
)
SELECT *
FROM Deviation;

特點(diǎn)

• 像“流水線”
• 邏輯拆得非常清楚
• 強(qiáng)烈推薦在復(fù)雜分析 SQL 中使用

3?? 遞歸 CTE（Recursive CTE）??

?? SQL Server 中最“高級(jí)”的 CTE

典型用途

• 樹結(jié)構(gòu)
• 層級(jí)關(guān)系
• 步進(jìn) / 迭代計(jì)算

示例 1：遞歸生成數(shù)字

WITH NumCTE AS (
    SELECT 1 AS N
    UNION ALL
    SELECT N + 1
    FROM NumCTE
    WHERE N < 10
)
SELECT * FROM NumCTE;

?? SQL 里沒有 for 循環(huán)？遞歸 CTE 就是

示例 2：層級(jí)結(jié)構(gòu)（父子關(guān)系）

WITH OrgCTE AS (
    SELECT ID, ParentID, Name, 0 AS Level
    FROM Org
    WHERE ParentID IS NULL

    UNION ALL

    SELECT o.ID, o.ParentID, o.Name, c.Level + 1
    FROM Org o
    JOIN OrgCTE c ON o.ParentID = c.ID
)
SELECT * FROM OrgCTE;

4?? 用 CTE 做 UPDATE / DELETE / INSERT

?? CTE 不只是 SELECT

UPDATE + CTE

WITH BadData AS (
    SELECT *
    FROM TestData
    WHERE Power > 10
)
UPDATE BadData
SET Power = 10;

DELETE + CTE

WITH DupSN AS (
    SELECT SN
    FROM TestData
    GROUP BY SN
    HAVING COUNT(*) > 1
)
DELETE FROM TestData
WHERE SN IN (SELECT SN FROM DupSN);

?? 在清理測(cè)試數(shù)據(jù)時(shí)非常實(shí)用

5?? CTE + 窗口函數(shù)（工程最常見組合）?

?? 你在光模塊測(cè)試數(shù)據(jù)分析里會(huì)經(jīng)常用

WITH Stat AS (
    SELECT
        SN,
        Power,
        AVG(Power) OVER(PARTITION BY SN) AS AvgPower,
        STDEV(Power) OVER(PARTITION BY SN) AS StdDev
    FROM TestData
)
SELECT *
FROM Stat
WHERE ABS(Power - AvgPower) > 3 * StdDev;

??

• 比子查詢清晰
• 比臨時(shí)表輕量

三、CTE 和其他“表表達(dá)式”的關(guān)系（重點(diǎn)）

SQL Server 中的 表表達(dá)式家族

?? CTE ≈ “一次性視圖”

四、CTE 使用中的幾個(gè)“坑”（很重要）

? 1. CTE 不能跨語句使用

WITH A AS (...)
SELECT * FROM A;

SELECT * FROM A; -- ? 不行

? 2. CTE 默認(rèn)不保證執(zhí)行順序

• SQL Server 會(huì)自動(dòng)優(yōu)化
• 不要指望 CTE 像臨時(shí)表一樣“先算完”

? 3. 遞歸 CTE 有默認(rèn)深度限制

OPTION (MAXRECURSION 1000);

五、你在“測(cè)試數(shù)據(jù)分析”里最值得用的 CTE 場(chǎng)景

結(jié)合你現(xiàn)在的工作（光模塊 / RF / 測(cè)試數(shù)據(jù)）：

? Deviation / 統(tǒng)計(jì)分析
? 多步測(cè)試結(jié)果拆分
? 異常點(diǎn)篩選
? SN / Lot / Step 分層分析
? 替代 VB / C# 里的中間計(jì)算

六、總結(jié)一句話（給你記憶用）

CTE 只有一種語法，但至少有 5 種工程級(jí)用法

1?? 普通 CTE
2?? 多 CTE 鏈?zhǔn)?br> 3?? 遞歸 CTE
4?? CTE + DML（Update/Delete）
5?? CTE + 窗口函數(shù)

如果你愿意，下一步我可以幫你：

• ?? 把 你現(xiàn)有的某個(gè) SQL（Deviation 相關(guān)）重構(gòu)成 CTE
• ?? 對(duì)比 CTE vs 臨時(shí)表 在測(cè)試數(shù)據(jù)分析中的性能
• ?? 給你一套 “測(cè)試數(shù)據(jù)分析專用 CTE 模板”

你更想看哪一個(gè)？