昨天有探討了分段費用的計算,是直接計算出來的.
但是人是好奇的動物,雖說電腦已經普遍運用在日常生活中,
也取得大家一定程度的信任,但是對於賬單,單據等等,
大家還是喜歡能夠看到細目,了解其計算過程,這樣賬單
就具有較高的可讀性.
依據昨天的三個TABLE,可以建立如下的VIEW.

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

搭配VIEW使用,就很容易讓前端的程式展現細項與總計.