DAY 26
9

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

``````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, '葵つかさ');
``````

``````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);
``````

``````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 |
+--------------------+--------+
``````

MySQL那些事兒30

1 則留言

0

iT邦研究生 1 級 ‧ 2013-10-26 21:04:52