iT邦幫忙

DAY 26
8

MySQL那些事兒系列 第 26

應用實例(三) 分段費用計算

價格是會隨時間變化的,不管是採購物品,或是人力服務等.
故費用的計算要考慮時間因素.
假設泰大聘請服務人員進行家事服務.
人員TABLE及資料如下

CREATE TABLE Girls(
galid INT UNSIGNED NOT NULL PRIMARY KEY,
name  VARCHAR(20) NOT NULL
);

INSERT INTO Girls(galid, name) VALUES
(1, '初音みのり'),
(2, '桜木凛'),
(3, '希崎ジェシカ'),
(4, '葵つかさ');

時薪費率TABLE及資料如下

CREATE TABLE Billings(
galid     INT UNSIGNED NOT NULL,
bill_date DATE NOT NULL,
bill_rate INT UNSIGNED NOT NULL,
PRIMARY KEY(galid, bill_date)
);

INSERT INTO Billings(galid, bill_date, bill_rate) VALUES
(1, '2013-01-01', 25),
(2, '2013-01-01', 26),
(3, '2013-01-01', 24),
(4, '2013-01-01', 27),
(1, '2013-07-01', 30),
(2, '2013-07-01', 32),
(4, '2013-08-01', 35);

工作紀錄TABLE及資料如下

CREATE TABLE Worklog(
galid     INT UNSIGNED NOT NULL,
work_date DATE NOT NULL,
work_hour TINYINT UNSIGNED NOT NULL,
PRIMARY KEY (galid, work_date)
);

INSERT INTO Worklog(galid, work_date, work_hour) VALUES
(1, '2013-02-01', 5),
(2, '2013-02-02', 4),
(3, '2013-03-01', 6),
(4, '2013-06-01', 3),
(1, '2013-08-01', 7),
(2, '2013-08-02', 6),
(3, '2013-08-03', 8),
(4, '2013-09-01', 5);

那泰大要怎樣計算費用呢?

可以用以下這道指令.

SELECT name
     , SUM(W1.work_hour * B1.bill_rate) AS '費用'
  FROM Girls AS G1
     , Billings AS B1
     , Worklog AS W1
 WHERE G1.galid = B1.galid
   AND G1.galid = W1.galid
   AND bill_date = (SELECT MAX(bill_date)
                      FROM Billings AS B2
                     WHERE B2.galid = G1.galid
                       AND B2.bill_date <= W1.work_date)
   AND W1.work_date >= bill_date
 GROUP BY G1.galid;

+--------------------+--------+
| name               | 費用   |
+--------------------+--------+
| 初音みのり          |    335 |
| 桜木凛             |    296 |
| 希崎ジェシカ        |    336 |
| 葵つかさ            |    256 |
+--------------------+--------+

這樣泰大就能輕鬆的計算出來該付給MM們多少摳摳了.


上一篇
應用實例(二) 時間區間的合併
下一篇
應用實例(四) 分段費用計算再次探討
系列文
MySQL那些事兒30

1 則留言

我要留言

立即登入留言