建立視圖
CREATE VIEW view_name AS
SELECT t1.xxx, t2.xxx, t3.xxx
FROM (table1 t1 INNER JOIN table2 t2 ON t1.fid = t2.fid)
INNER JOIN table3 t3 ON t1.mid = t3.mid;
多表 INNER JOIN 遞歸嵌套寫法技巧
- 先寫最內層兩表關聯
- 用括號將
FROM 後至結尾包裹
- 在右括號後接續下一張表的
INNER JOIN
- 依此類推，支援任意 N 表關聯
標準模板
-- 兩表
FROM table1 INNER JOIN table2 ON table1.col = table2.col
-- 三表
FROM (table1 INNER JOIN table2 ON table1.col = table2.col)
INNER JOIN table3 ON table1.col = table3.col
-- 四表
FROM ((table1 INNER JOIN table2 ON table1.col = table2.col)
INNER JOIN table3 ON table1.col = table3.col)
INNER JOIN table4 ON table1.col = table4.col
-- 五表
FROM (((table1 INNER JOIN table2 ON table1.col = table2.col)
INNER JOIN table3 ON table1.col = table3.col)
INNER JOIN table4 ON table1.col = table4.col)
INNER JOIN table5 ON table1.col = table5.col
刪除視圖
DROP VIEW view_name;
注意事項
- 所有標點、關鍵字、空格必須為半角英文
- 被多表關聯的「主表」欄位建議為數值型，且關聯欄位在對方表為主鍵/唯一索引
- 嵌套層級過深可能影響解析效能，建議 5 表以內；超過請考慮臨時表或 CTE (MySQL 8.0+)
