資料庫作業有一道題是這樣子的：
有一張名叫emp的表記錄員工資訊，其中有如下欄位 HIREDATE 表示員工被雇用的日期：
然后問題是這樣的：
q7. Show details of employee hiredates and the date of their first payday.
(Paydays occur on the last Friday of each month)
(plus their names)
意思就是每個月的最后一個周五是發工資的日子，要我們輸出他們領到第一桶金的日子，（這里其實是計算當月payday，不考慮hiredate超過payday的情況）
那么怎么通過一個日期計算出當月的最后一個周五呢？我們用最暴力的方法，直接迭代計算：
思路：
- LAST_DAY 函式找當月最后一天 x
- 日期 x 逐天減少
- 直到 x 等于周五，即是最后一個周五
代碼：
DELIMITER $$ # 結束符由 ; 改為 $$ ，因為函式中間需要用到 ; 號
DROP FUNCTION IF EXISTS last_friday $$ # 洗掉之前定義的函式 last_friday
CREATE FUNCTION last_friday (dt DATE) RETURNS DATE # 定義函式 last_friday 有一個DATE類的形參 dt，回傳DATE物件
BEGIN
DECLARE last DATE; # 臨時變數 last
SET last=LAST_DAY(dt); # last=dt的最后一天
WHILE DATE_FORMAT(last, "%W")!="Friday" DO # 不斷判斷是否為周五
SET last=DATE_SUB(last, interval 1 day); # 迭代減少天數
END WHILE;
RETURN last; # 回傳結果
END $$
DELIMITER ; # 結束符由 $$ 改為 ; 號
# 不帶注釋 ↓
DELIMITER $$
DROP FUNCTION IF EXISTS last_friday $$
CREATE FUNCTION last_friday (dt DATE) RETURNS DATE
BEGIN
DECLARE last DATE;
SET last=LAST_DAY(dt);
WHILE DATE_FORMAT(last, "%W")!="Friday" DO
SET last=DATE_SUB(last, interval 1 day);
END WHILE;
RETURN last;
END $$
DELIMITER ;
然后我們就可以通過 last_friday 函式進行查詢：
SELECT ENAME, HIREDATE, last_friday(HIREDATE) FROM emp;
結果：
然后下一題：
q8. Refine your answer to 7 such that it works
even if an employee is hired after the last Friday of the month
(cf Martin)
意思是有一些員工是在payday之后入職的，比如：
那么我們應該找下一個payday，思路也很簡單，通過 if 陳述句進行判斷即可，
- 如果
當月payday大于入職日期hiredate，回傳當月payday - 如果
當月payday小于入職日期hiredate，回傳下個月的payday
我們首先撰寫一個函式next_friday，計算下個月的payday：
DELIMITER $$
DROP FUNCTION IF EXISTS next_friday $$
CREATE FUNCTION next_friday (dt DATE) RETURNS DATE
BEGIN
DECLARE last DATE;
SET last=LAST_DAY(DATE_ADD(dt, interval 1 month));
WHILE DATE_FORMAT(last, "%W")!="Friday" DO
SET last=DATE_SUB(last, interval 1 day);
END WHILE;
RETURN last;
END $$
DELIMITER ;
這個函式和 last_friday 沒啥區別，就是計算下個月而已：
隨后我們修改 last_friday ，最后回傳時加一個判斷即可：
DELIMITER $$
DROP FUNCTION IF EXISTS last_friday $$
CREATE FUNCTION last_friday (dt DATE) RETURNS DATE
BEGIN
DECLARE last DATE;
SET last=LAST_DAY(dt);
WHILE DATE_FORMAT(last, "%W")!="Friday" DO
SET last=DATE_SUB(last, interval 1 day);
END WHILE;
RETURN IF(last>dt, last, next_friday(dt));
END $$
DELIMITER ;
注意這個 if 運算式即可：
if(expr, case1, case2)
if(條件, 情況1, 情況2)
隨后再次運行查詢：
舒服了
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/170041.html
標籤：其他
上一篇：關系型資料庫（Relational Database）與非關系型資料庫（NoSQL）的區別：（MySQL，Redis，Memcache，MongoDB）
下一篇：mysql資料庫初學者簡單操作