我在 MySQL 中有兩個表。第一個表“table1”記錄了特定專案一個月中所有天的每日資料。像這樣 -
| ID | 物品 | 日期 | num_of_items |
|---|---|---|---|
| 1 | 包 | 2021-12-01 | 2 |
| 2 | 包 | 2021-12-02 | 3 |
| 3 | 包 | 2021-12-03 | 4 |
| 4 | 包 | 2021-12-04 | 2 |
| 5 | 包 | 2021-12-05 | 7 |
| 6 | 鉛筆 | 2021-12-01 | 2 |
| 7 | 鉛筆 | 2021-12-02 | 4 |
| 8 | 鉛筆 | 2021-12-03 | 1 |
| 9 | 鉛筆 | 2021-12-04 | 5 |
| 10 | 鉛筆 | 2021-12-05 | 2 |
如果當月有任意數量的專案需要，這將持續到第 31 天。
第二個表“table2”包含專案的費率，因為它在一個月內發生變化并且不是恒定的。看起來是這樣的——
| ID | 月 | 年 | 物品 | 入境日期 | 最后日期 | 速度 |
|---|---|---|---|---|---|---|
| 1 | 十二月 | 2021年 | 包 | 2021-12-01 | 2021-12-02 | 10 |
| 2 | 十二月 | 2021年 | 包 | 2021-12-03 | 2021-12-05 | 12 |
| 3 | 十二月 | 2021年 | 鉛筆 | 2021-12-01 | 2021-12-03 | 5 |
| 4 | 十二月 | 2021年 | 鉛筆 | 2021-12-04 | 2021-12-05 | 3 |
例如，要計算當月 Bag's 的總價值，前兩天的數量為 Day 1 Day 2 的比率為 10；10x6 = 60 加上剩余 3 天的 12 的費率 - 12x(2 5 1) = 96 總共 156 個行李。
我想用該月每個專案的總值更新第三個表“table3”，當它用觸發器更新時。第三張桌子看起來像這樣 -
| ID | 物品 | 月 | 年 | 全部的 |
|---|---|---|---|---|
| 1 | 包 | 12 | 2021年 | 156 |
| 2 | 鉛筆 | 12 | 2021年 | 56 |
我知道如何手動獲取我想要的值并用它更新 table3，但不知道如何使用觸發器自動執行此操作，該觸發器在資料插入或更新到 table2 時更新 table3。
我該如何去做這樣的事情？
uj5u.com熱心網友回復：
這是一個日期范圍查找問題。
您的第一步是創建一個結果集，其中包含table1具有適當ratefrom的行table2。適當的速率通過找到所確定的table2與包含的時間范圍行date的table1。
用 JOIN 的 ON 子句做到這一點。（在這里小提琴）
SELECT o.id, o.item,
MONTH(o.date) month,
YEAR(o.date) year,
o.num_of_items,
r.rate
FROM table1 o
LEFT JOIN table2 r
ON o.item=r.item
AND o.date >= r.entry_date
AND o.date <= r.final_date;
查詢的最后兩行是您需要放置table1.date在日期范圍內的技巧。聰明的開發人員會注意這個結果集以確保它是正確的。（那個日期范圍的東西可能很難做到正確。）
然后，很容易將查詢轉換為 GROUP BY。（小提琴）
SELECT o.item,
MONTH(o.date) month,
YEAR(o.date) year,
SUM(o.num_of_items * r.rate) total
FROM table1 o
LEFT JOIN table2 r
ON o.item = r.item
AND o.date >= r.entry_date
AND o.date <= r.final_date
GROUP BY o.item, MONTH(o.date), YEAR(o.date);
還有一件事。您可能明智地避免使用觸發器并使用此資訊更新第三個表。相反，使用視圖。這樣你的資料就不會不一致。（小提琴）
CREATE OR REPLACE VIEW totals AS
SELECT o.item,
MONTH(o.date) month,
YEAR(o.date) year,
SUM(o.num_of_items * r.rate) total
FROM table1 o
LEFT JOIN table2 r
ON o.item = r.item
AND o.date >= r.entry_date
AND o.date <= r.final_date
GROUP BY o.item, MONTH(o.date), YEAR(o.date);
SELECT * FROM totals;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/390724.html