MySQL 多表聯合查詢：查詢用戶 ID=0 的總消費金額

表結構

goods(id PK, price, name)
buyer(id PK, name)
buyRecord(id PK, buyerId FK, goodsId FK)

SQL 語句

SELECT SUM(g.price) AS total_amount
FROM goods g
INNER JOIN buyRecord br ON g.id = br.goodsId
WHERE br.buyerId = 0;

解析

• INNER JOIN 連接商品表與購買記錄表，匹配 goods.id = buyRecord.goodsId。
• WHERE br.buyerId = 0 篩選指定用戶的購買記錄。
• SUM(g.price) 聚合該用戶購買的所有商品價格。
• 若需顯示用戶名，可再 JOIN buyer 表。

擴展：含購買數量

SELECT b.name, SUM(g.price * br.quantity) AS total
FROM buyRecord br
JOIN goods g ON g.id = br.goodsId
JOIN buyer b ON b.id = br.buyerId
WHERE br.buyerId = 0
GROUP BY b.id, b.name;

（假設 buyRecord 增加 quantity 字段）

索引建議

CREATE INDEX idx_buyRecord_buyerId ON buyRecord(buyerId);
CREATE INDEX idx_buyRecord_goodsId ON buyRecord(goodsId);