MySQL 找每個月最後一個星期五 -- 函數定義與使用

資料庫作業有一道題是這樣子的：

有一張名叫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的情況）

那麼怎麼通過一個日期計算出當月的最後一個週五呢？我們用最暴力的方法，直接迭代計算：

思路：

1. LAST_DAY 函數找當月最後一天 x
2. 日期 x 逐天減少
3. 直到 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 語句進行判斷即可。

1. 如果當月payday大於入職日期hiredate，返回當月payday
2. 如果當月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)

隨後再次執行查詢：

舒服了