MySQLで日付から「○月第○週」を求める方法

MySQLで日付から「○月第○週」を求める方法のメモ。

-- テスト用テーブル・データ作成
create table test.weektest (date1 date primary key);

insert into test.weektest values('2017/06/01');
insert into test.weektest values('2017/06/02');
insert into test.weektest values('2017/06/03');
insert into test.weektest values('2017/06/04');
insert into test.weektest values('2017/06/05');
insert into test.weektest values('2017/06/06');
insert into test.weektest values('2017/06/07');
insert into test.weektest values('2017/06/08');
insert into test.weektest values('2017/06/09');
insert into test.weektest values('2017/06/10');
insert into test.weektest values('2017/06/11');
insert into test.weektest values('2017/06/12');
insert into test.weektest values('2017/06/13');
insert into test.weektest values('2017/06/14');
insert into test.weektest values('2017/06/15');
insert into test.weektest values('2017/06/16');
insert into test.weektest values('2017/06/17');
insert into test.weektest values('2017/06/18');
insert into test.weektest values('2017/06/19');
insert into test.weektest values('2017/06/20');
insert into test.weektest values('2017/06/21');
insert into test.weektest values('2017/06/22');
insert into test.weektest values('2017/06/23');
insert into test.weektest values('2017/06/24');
insert into test.weektest values('2017/06/25');
insert into test.weektest values('2017/06/26');
insert into test.weektest values('2017/06/27');
insert into test.weektest values('2017/06/28');
insert into test.weektest values('2017/06/29');
insert into test.weektest values('2017/06/30');
insert into test.weektest values('2017/07/01');
insert into test.weektest values('2017/07/02');
insert into test.weektest values('2017/07/03');
insert into test.weektest values('2017/07/04');
insert into test.weektest values('2017/07/05');
insert into test.weektest values('2017/07/06');
insert into test.weektest values('2017/07/07');
insert into test.weektest values('2017/07/08');
insert into test.weektest values('2017/07/09');
insert into test.weektest values('2017/07/10');
insert into test.weektest values('2017/07/11');
insert into test.weektest values('2017/07/12');
insert into test.weektest values('2017/07/13');
insert into test.weektest values('2017/07/14');
insert into test.weektest values('2017/07/15');
insert into test.weektest values('2017/07/16');
insert into test.weektest values('2017/07/17');
insert into test.weektest values('2017/07/18');
insert into test.weektest values('2017/07/19');
insert into test.weektest values('2017/07/20');
insert into test.weektest values('2017/07/21');
insert into test.weektest values('2017/07/22');
insert into test.weektest values('2017/07/23');
insert into test.weektest values('2017/07/24');
insert into test.weektest values('2017/07/25');
insert into test.weektest values('2017/07/26');
insert into test.weektest values('2017/07/27');
insert into test.weektest values('2017/07/28');
insert into test.weektest values('2017/07/29');
insert into test.weektest values('2017/07/30');
insert into test.weektest values('2017/07/31');
insert into test.weektest values('2017/08/01');
insert into test.weektest values('2017/08/02');
insert into test.weektest values('2017/08/03');
insert into test.weektest values('2017/08/04');
insert into test.weektest values('2017/08/05');
insert into test.weektest values('2017/08/06');
insert into test.weektest values('2017/08/07');
insert into test.weektest values('2017/08/08');
insert into test.weektest values('2017/08/09');
insert into test.weektest values('2017/08/10');

SELECT
date1
,CONCAT(MONTH(ADDDATE(date1, weekday(date1) * -1))
  , '月第'
  , FLOOR((DAYOFMONTH(ADDDATE(date1, weekday(date1) * -1))-1)/7)+1, '週') 
AS WEEK
FROM test.weektest
ORDER BY date1;

↓のような結果が得られる。月曜始まりとして、ある日付の週の月曜日がある月の第○週を返す。

date1 WEEK
2017/06/01　5月第5週
2017/06/02　5月第5週
2017/06/03　5月第5週
2017/06/04　5月第5週
2017/06/05　6月第1週
2017/06/06　6月第1週
2017/06/07　6月第1週
2017/06/08　6月第1週
2017/06/09　6月第1週
2017/06/10　6月第1週
2017/06/11　6月第1週
2017/06/12　6月第2週
2017/06/13　6月第2週
2017/06/14　6月第2週
2017/06/15　6月第2週
2017/06/16　6月第2週
2017/06/17　6月第2週
2017/06/18　6月第2週
2017/06/19　6月第3週
2017/06/20　6月第3週
2017/06/21　6月第3週
2017/06/22　6月第3週
2017/06/23　6月第3週
2017/06/24　6月第3週
2017/06/25　6月第3週
2017/06/26　6月第4週
2017/06/27　6月第4週
2017/06/28　6月第4週
2017/06/29　6月第4週
2017/06/30　6月第4週
2017/07/01　6月第4週
2017/07/02　6月第4週
2017/07/03　7月第1週
2017/07/04　7月第1週
2017/07/05　7月第1週
2017/07/06　7月第1週
2017/07/07　7月第1週
2017/07/08　7月第1週
2017/07/09　7月第1週
2017/07/10　7月第2週
2017/07/11　7月第2週
2017/07/12　7月第2週
2017/07/13　7月第2週
2017/07/14　7月第2週
2017/07/15　7月第2週
2017/07/16　7月第2週
2017/07/17　7月第3週
2017/07/18　7月第3週
2017/07/19　7月第3週
2017/07/20　7月第3週
2017/07/21　7月第3週
2017/07/22　7月第3週
2017/07/23　7月第3週
2017/07/24　7月第4週
2017/07/25　7月第4週
2017/07/26　7月第4週
2017/07/27　7月第4週
2017/07/28　7月第4週
2017/07/29　7月第4週
2017/07/30　7月第4週
2017/07/31　7月第5週
2017/08/01　7月第5週
2017/08/02　7月第5週
2017/08/03　7月第5週
2017/08/04　7月第5週
2017/08/05　7月第5週
2017/08/06　7月第5週
2017/08/07　8月第1週
2017/08/08　8月第1週
2017/08/09　8月第1週
2017/08/10　8月第1週