在實務應用上,預算與實際已發生費用的計算,
是常要開發的功能.
故模擬實際可能發生的狀況,並提出解法,
開發系統時可參考之.
範例資料表

預算TABLE,包含有專案編號(task),科目(category),
預計費用(est_cost).

CREATE TABLE Budgeted(
task INT UNSIGNED NOT NULL PRIMARY KEY,
category CHAR(4) NOT NULL,
est_cost DECIMAL(8,2) NOT NULL
);

INSERT INTO Budgeted(task, category, est_cost) VALUES
(1, "9100", 100.00),
(2, "9100", 15.00),
(3, "9100", 6.00),
(4, "9200", 8.00),
(5, "9200", 11.00);

實際費用TABLE,包含有憑證編號(voucher),專案編號(task),
實際費用(act_cost).

CREATE TABLE Actual(
voucher INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
task INT UNSIGNED NOT NULL,
act_cost DECIMAL(8,2) NOT NULL,
CONSTRAINT FOREIGN KEY (task) REFERENCES Budgeted(task)
);

INSERT INTO Actual(task, act_cost) VALUES
(1, 10.00),
(1, 20.00),
(1, 15.00),
(2, 32.00),
(4, 8.00),
(5, 3.00),
(5, 4.00);

專案3,尚未有任何費用發生,每個專案會有多項費用,以模擬實際情況的可能性.

欲將每一個科目的預算與目前費用支出情形,以科目統計.
可以用以下的Query.

SELECT B1.category
     , SUM(B1.est_cost) AS estimated
     , (SELECT SUM(T1.act_cost)
          FROM Actual AS T1 
         WHERE T1.Task 
       BETWEEN MIN(B1.task) 
           AND MAX(B1.task)) AS spent
  FROM Budgeted AS B1
 GROUP BY B1.category;

+----------+-----------+-------+
| category | estimated | spent |
+----------+-----------+-------+
| 9100     |    121.00 | 77.00 |
| 9200     |     19.00 | 15.00 |
+----------+-----------+-------+

這樣就能輕鬆得到各科目的預算與實際費用情形,
爾後再配合前端程式或報表工具,抑或使用試算表
等工具,也可以繪製圖表,作成生動的報告.