SQL 兩表聯合計算：根據工作證號查詢人員稱號對應的研究生支持指標並求和

問題描述

兩張表：

• 表一：人員與稱號代碼對應（工號、姓名、稱號代碼）
• 表二：稱號代碼對應指標（稱號代碼、研究生支持人數、研究生支持年限）

需求：輸入姓名（如「張三」），查出其所有稱號代碼，關聯表二獲取「人數 × 年限」並求和。示例：張三結果 = 1×1 + 2×3 + 1×1 = 8。

標準 SQL 寫法

SELECT SUM(t2.研究生支持人數 * t2.研究生支持年限) AS total_score
FROM 表二 t2
WHERE t2.稱號代碼 IN (
    SELECT 稱號代碼 FROM 表一 WHERE 姓名 = '張三'
);

進階：使用 JOIN（效能通常更好）

SELECT SUM(t2.研究生支持人數 * t2.研究生支持年限) AS total_score
FROM 表一 t1
JOIN 表二 t2 ON t1.稱號代碼 = t2.稱號代碼
WHERE t1.姓名 = '張三';

關鍵點解析

• 子查詢 vs JOIN：現代優化器對簡單 IN 子查詢與 JOIN 通常生成相同執行計劃。JOIN 寫法更直觀、易擴展（如需顯示稱號名稱）。
• 聚合函數 SUM：直接對乘積求和，無需應用層循環累加。
• 索引建議：表一(姓名)、表一(稱號代碼)、表二(稱號代碼) 建立索引，加速關聯與篩選。
• 空值處理：若人數或年限可能為 NULL，使用 COALESCE(人數,0) * COALESCE(年限,0) 避免結果為 NULL。

擴展：查詢所有人員排名

SELECT t1.姓名, SUM(COALESCE(t2.研究生支持人數,0) * COALESCE(t2.研究生支持年限,0)) AS total_score
FROM 表一 t1
LEFT JOIN 表二 t2 ON t1.稱號代碼 = t2.稱號代碼
GROUP BY t1.姓名
ORDER BY total_score DESC;

圖片參考

總結

兩表聯合計算核心是「關聯 → 乘積 → 聚合」。使用 JOIN + SUM(expr) 單條 SQL 即可高效完成，避免應用層多次查詢循環。注意索引與空值處理，可直接用於報表、排名等場景。