實用 SQL 腳本精選：樞紐分析、字串處理、重複查詢、批次更新

1. 資料樞紐：行轉列 (PIVOT) 與列轉行 (UNPIVOT)

行轉列：季度業績橫向展開

CREATE TABLE test (id INT, name VARCHAR(20), quarter INT, profile INT)
INSERT test VALUES
(1,'a',1,1000),(1,'a',2,2000),(1,'a',3,4000),(1,'a',4,5000),
(2,'b',1,3000),(2,'b',2,3500),(2,'b',3,4200),(2,'b',4,5500)

SELECT id, name,
       [1] AS [一季度], [2] AS [二季度], [3] AS [三季度], [4] AS [四季度]
FROM test
PIVOT (SUM(profile) FOR quarter IN ([1],[2],[3],[4])) AS pvt

列轉行：將寬表還原為長表

CREATE TABLE test2 (id INT, name VARCHAR(20), Q1 INT, Q2 INT, Q3 INT, Q4 INT)
INSERT test2 VALUES (1,'a',1000,2000,4000,5000), (2,'b',3000,3500,4200,5500)

SELECT id, name, quarter, profile
FROM test2
UNPIVOT (profile FOR quarter IN (Q1,Q2,Q3,Q4)) AS unpvt

2. 字串替換與遮罩技巧

-- 遮罩姓名中間字元
UPDATE tbPersonalInfo SET TrueName = REPLACE(TrueName, SUBSTRING(TrueName,2,4), '**') WHERE ID = 1

-- 遮罩手機中間 8 碼
UPDATE tbPersonalInfo SET Mobile = REPLACE(Mobile, SUBSTRING(Mobile,4,8), '********') WHERE ID = 1

-- 替換特定字串
UPDATE tbPersonalInfo SET Email = REPLACE(Email, 'chinamobile', '******') WHERE ID = 1

3. 查詢重複記錄 (HAVING / EXISTS / JOIN)

單一欄位判斷重複

SELECT * FROM 表 WHERE ID IN (
    SELECT ID FROM 表 GROUP BY ID HAVING COUNT(*) > 1
)

多欄位組合判斷重複

SELECT * FROM 表 WHERE ID1+ID2+ID3 IN (
    SELECT ID1+ID2+ID3 FROM 表 GROUP BY ID1,ID2,ID3 HAVING COUNT(*) > 1
)

EXISTS 相關子查詢法（效能較佳）

-- 方法 1
SELECT * FROM zy_bho a WHERE EXISTS (
    SELECT 1 FROM zy_bho WHERE [PK] <> a.[PK] AND ZYH = a.ZYH
)
-- 方法 2：自我 JOIN
SELECT a.* FROM zy_bho a JOIN zy_bho b
  ON a.[pk] <> b.[pk] AND a.zyh = b.zyh
-- 方法 3：IN + GROUP BY
SELECT * FROM zy_bho WHERE zyh IN (
    SELECT zyh FROM zy_bho GROUP BY zyh HAVING COUNT(zyh) > 1
)

4. 多行彙總為單行多欄 (CASE 聚合)

SELECT
    DeptName = O.OUName,
    [9G]  = SUM(CASE WHEN PersonalGrade = 9 THEN 1 ELSE 0 END),
    [8G]  = SUM(CASE WHEN PersonalGrade = 8 THEN 1 ELSE 0 END),
    [7G4] = SUM(CASE WHEN PersonalGrade = 7 AND JobGrade = 4 THEN 1 ELSE 0 END),
    [7G3] = SUM(CASE WHEN PersonalGrade = 7 AND JobGrade = 3 THEN 1 ELSE 0 END),
    -- ... 更多等級組合
FROM ...
GROUP BY O.OUName

5. 資料表複製三種語法

注意：大量資料用 SELECT INTO 或 INSERT SELECT 直接執行，避免精靈記憶體不足問題。

6. 關聯子查詢 UPDATE 三種寫法

-- 方法 1：純量子查詢
UPDATE Table1 SET c = (SELECT c FROM Table2 WHERE a = Table1.a) WHERE c IS NULL

-- 方法 2：FROM 子句 JOIN
UPDATE A SET newqty = B.qty FROM A, B WHERE A.bnum = B.bnum

-- 方法 3：衍生表 UPDATE
UPDATE (SELECT A.bnum, A.newqty, B.qty FROM A LEFT JOIN B ON A.bnum = B.bnum) AS C
SET C.newqty = C.qty WHERE C.bnum = XX

7. 遠端伺服器查詢 (OPENROWSET)

-- 方式 1：連線字串
SELECT * FROM OPENROWSET('SQLOLEDB', 'server=192.168.0.67;uid=sa;pwd=password', 'SELECT * FROM BCM2.dbo.tbAppl')
-- 方式 2：分離參數
SELECT * FROM OPENROWSET('SQLOLEDB', '192.168.0.67', 'sa', 'password', 'SELECT * FROM BCM2.dbo.tbAppl')

8. TRUNCATE TABLE vs DELETE

適用：清空測試表、重置種子、大量資料快速清除。生產刪除仍建議用 DELETE 配合 WHERE 分批執行。