SQL 行轉列實戰：將 RE/RG 開頭數據拆分為兩列

需求場景

表結構：id, a, b。id 非唯一，a 含 RE% 和 RG% 兩類前綴，b 為 0/1 結果。需查詢結果為：每行一個 id，兩列分別顯示 RE 和 RG 對應的 a、b 值。

核心思路

1. 用子查詢分別篩選 RE% 和 RG% 記錄。
2. 以 id 為主鍵，SUBSTRING(a, 3) 為匹配鍵（去掉前綴後的編碼）進行 RIGHT JOIN，保證 RG 為主表，RE 為從表。
3. 選取所需列，別名區分。

標準 SQL 寫法

SELECT
    rg.id,
    re.a AS re_a,
    re.b AS re_b,
    rg.a AS rg_a,
    rg.b AS rg_b
FROM
    (SELECT * FROM test WHERE a LIKE 'RE%') re
RIGHT JOIN
    (SELECT * FROM test WHERE a LIKE 'RG%') rg
    ON re.id = rg.id
   AND SUBSTRING(re.a, 3) = SUBSTRING(rg.a, 3);

進階技巧

• MySQL 8.0+ 可用 WITH (CTE) 提升可讀性。
• PostgreSQL/Oracle 可用 crosstab / PIVOT 內置函數更簡潔。
• 若需顯示所有 id（即使無 RG），改用 FULL JOIN 或 UNION ALL + 聚合。

性能提示

在 a 列建立前綴索引 INDEX idx_a_prefix (a(10))，並確保 id 有索引，大幅加速 JOIN。