您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 視圖實戰：多子查詢 + 多 LEFT JOIN 構建用戶管理視圖

編輯：MySQL 綜合教學

業務場景

需構建一個用戶管理視圖 v_usermanage，聚合顯示：

用戶基本信息；
本月/上月消費金額；
未出帳金額、總獎金；
僅包含 provide = '0' 的區段記錄。

分步構建策略

1. 基礎過濾視圖

CREATE VIEW `v_noprovide` AS
SELECT * FROM tb_section WHERE provide = '0';

2. 本月消費彙總視圖

CREATE VIEW `v_thismonth` AS
SELECT ts.userId AS id,
       CONCAT('thismonth:', SUM(ts.amount), 'yuan') AS thismonth
FROM tb_section AS ts
WHERE ts.yearMonth = DATE_FORMAT(NOW(), '%Y%m');

3. 上月消費彙總視圖

CREATE VIEW `v_lastmonth` AS
SELECT ts.userId AS id,
       CONCAT('lastmonth:', SUM(ts.amount), 'yuan') AS lastmonth
FROM tb_section AS ts
WHERE ts.yearMonth = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y%m');

4. 最終聚合視圖

CREATE VIEW `v_usermanage` AS
SELECT u.id AS id,
       u.email AS email,
       u.`name` AS `name`,
       CONCAT(
         COALESCE(la.lastmonth, 'lastmonth:0yuan'),
         COALESCE(th.thismonth, 'thismonth:0yuan')
       ) AS recent,
       SUM(COALESCE(b.amount, 0)) AS totalBonus,
       SUM(COALESCE(n.amount, 0)) AS unbilled
FROM tb_user AS u
LEFT JOIN tb_bonus AS b       ON u.id = b.receiverId
LEFT JOIN v_noprovide AS n    ON u.id = n.userId
LEFT JOIN v_thismonth AS th   ON u.id = th.id
LEFT JOIN v_lastmonth AS la   ON u.id = la.id
GROUP BY u.id;

關鍵技術點解析

技術點	作用
`DATE_FORMAT(NOW(), '%Y%m')`	動態獲取當前年月，無需硬編碼
`DATE_SUB(NOW(), INTERVAL 1 MONTH)`	精確計算上月，跨年自動處理
`COALESCE(col, default)`	將 NULL 替換為友好提示，避免前端判空
`CONCAT(...)`	拼接多字段為單一展示列
多層 `LEFT JOIN`	保留所有用戶，無匹配行自動填 NULL
視圖嵌套	將複雜邏輯拆解為可復用、可維護的單元

效能與維護建議

索引覆蓋：確保 tb_section.userId、yearMonth、provide 有組合索引；tb_bonus.receiverId 有索引。
避免 SELECT *：基礎視圖僅選取必要列，減少臨時表開銷。
物化考量：若數據量大、查詢頻繁，考慮改為物化表 + 定時刷新，或使用 CREATE TABLE ... AS SELECT 方案。
版本兼容：MySQL 5.7+ 支持視圖中引用視圖，5.6 需確認 derived_merge 優化器開關。