DAY 27
12

## 應用實例(四) 分段費用計算再次探討

``````CREATE VIEW TedGalRpt (
galid
, name
, work_date
, work_hour
, bill_rate)
AS
SELECT W1.galid
, name
, work_date
, work_hour,
(SELECT bill_rate
FROM Billings AS B1
WHERE bill_rate = (SELECT MAX(bill_rate)
FROM Billings AS B2
WHERE B2.bill_date <= W1.work_date
AND B1.galid = B2.galid
AND B1.galid = W1.galid))
FROM Worklog AS W1
, Girls AS G1
WHERE G1.galid = W1.galid;
``````

``````SELECT * FROM TedGalRpt;
+-------+--------------------+------------+-----------+-----------+
| galid | name               | work_date  | work_hour | bill_rate |
+-------+--------------------+------------+-----------+-----------+
|     1 | 初音みのり         | 2013-02-01 |         5 |        25 |
|     1 | 初音みのり         | 2013-08-01 |         7 |        30 |
|     2 | 桜木凛             | 2013-02-02 |         4 |        26 |
|     2 | 桜木凛             | 2013-08-02 |         6 |        32 |
|     3 | 希崎ジェシカ       | 2013-03-01 |         6 |        24 |
|     3 | 希崎ジェシカ       | 2013-08-03 |         8 |        24 |
|     4 | 葵つかさ           | 2013-06-01 |         3 |        27 |
|     4 | 葵つかさ           | 2013-09-01 |         5 |        35 |
+-------+--------------------+------------+-----------+-----------+
``````

``````SELECT name
, work_date AS '工作日'
, work_hour AS '工時'
, bill_rate AS '單價'
, work_hour * bill_rate AS '費用'
FROM TedGalRpt;

+--------------------+------------+--------+--------+--------+
| name               | 工作日     | 工時   | 單價   | 費用   |
+--------------------+------------+--------+--------+--------+
| 初音みのり         | 2013-02-01 |      5 |     25 |    125 |
| 初音みのり         | 2013-08-01 |      7 |     30 |    210 |
| 桜木凛             | 2013-02-02 |      4 |     26 |    104 |
| 桜木凛             | 2013-08-02 |      6 |     32 |    192 |
| 希崎ジェシカ       | 2013-03-01 |      6 |     24 |    144 |
| 希崎ジェシカ       | 2013-08-03 |      8 |     24 |    192 |
| 葵つかさ           | 2013-06-01 |      3 |     27 |     81 |
| 葵つかさ           | 2013-09-01 |      5 |     35 |    175 |
+--------------------+------------+--------+--------+--------+
``````

``````SELECT name, SUM(work_hour * bill_rate) AS '費用'
FROM TedGalRpt
GROUP BY galid;

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

MySQL那些事兒30

### 1 則留言

0

iT邦高手 1 級 ‧ 2013-10-27 22:07:59