前提条件として、以下の二つのテーブルを持っています。
CREATE TABLE `mst_grade` (
`gradeId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'プライマリーキー',
`gradeName` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '学年名',
`gradeShortName` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '学年略名'
PRIMARY KEY (`gradeId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='[Master]学年マスタ';
INSERT INTO `mst_grade` (`gradeId`, `gradeName`, `gradeShortName`)
VALUES
(1,'小学1年','小1'),
(2,'小学2年','小2'),
(3,'小学3年','小3'),
(4,'小学4年','小4'),
(5,'小学5年','小5'),
(6,'小学6年','小6'),
(7,'中学1年','中1'),
(8,'中学2年','中2'),
(9,'中学3年','中3');
CREATE `tbl_student` (
`userid``userId` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'プライマリーキー',
`userName` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'ユーザー名',
`birthday` date DEFAULT NULL,
PRIMARY KEY (`userId`) COMMENT 'プライマリーキー'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='[User]ユーザーテーブル';
INSERT INTO `tbl_user` (`userId`, `userName`, `birthday`)
VALUES
(1, '生徒1', '2000-04-02'),
(2, '生徒2', '2001-03-31'),
(3, '生徒3', '2001-04-01');
サンプルデータの例では、いずれも同じ学年であるはずです。ポイントは、早生まれの生徒の学年をうまく算出することが肝となります。
考え方の道筋としては、年度末の日には全ての生徒は同じ年齢となっていることです。つまり今年度の末で満7歳の生徒(児童)は小学校1年生であるということです。
では、誕生日から年齢を算出することができれば、応用できそうですので、ググると…
-- birthday は DATE 型のカラムとする
SELECT
(YEAR(CURDATE()) - YEAR(birthday))
- (RIGHT(CURDATE(), 5) < RIGHT(birthday, 5)) AS age
FROM
hoge
これは、CURDATE() を使っているので、「現在」の年齢を算出しますので、基準日を今年度末日に変更します。
通常年度末は3月31日ですが、4月1日は早生まれの扱いなので、基準日4月1日時点での年齢を算出します。
と、記憶を頼りに判断したわけですが、都市伝説だといけないので調べてみました。
本日執筆時点(2015年3月8日)では、そのまま2015年4月1日が基準日となりますが、2015年4月2日になると、2016年度になるので、4月1日より大きい日付のときは翌年4月1日、小さいときは当年の4月1日は基準日となります。
そこで、これを表現したSQLが以下のようになります。
SELECT
(
IF(
'04-01' > RIGHT(curdate(), 5), YEAR(curdate()), YEAR(curdate()) + 1
) - YEAR(`birthday`))
- ('04-01' < RIGHT(`birthday`, 5)
) AS age
, `birthday`
FROM `tbl_user`
あとは、実際の年齢から6を減算すると、学年マスタのプラーマリーキーの値に一致します。
SELECT
(
IF(
'04-01' > RIGHT(curdate(), 5), YEAR(curdate()), YEAR(curdate()) + 1
) - YEAR(`birthday`))
- ('04-01' < RIGHT(`birthday`, 5)
) - 6 AS gradeId
, `birthday`
FROM `tbl_user`
サブクエリを使って表現すると、
SELECT
(
SELECT `gradeName` FROM `mst_grade` WHERE `gradeId` = (
(IF('04-01' > RIGHT(curdate(), 5), YEAR(curdate()), YEAR(curdate()) + 1) - YEAR(`birthday`))
- ('04-01' < RIGHT(`birthday`, 5))
- 6
)
)
AS `gradeName`
, `birthday`
FROM `tbl_user`
となりました。
うーん、なんか美しくないですね。件数が多いと処理重そうだし…
と、無理やりSQLでやったらどうなるかと、試行してみましたが、実際には使えるのか?
使えないでしょうね…。なぜっていうと、実際には特殊学級や発育不全などの理由で、必ずしも年齢と学年が一致するわけではありません。実際の運用では原則どおりにはいかないものだと思って、リンクテーブルで結合する方法が良いのではないかと思います。
余談ですが、はじめはJOINを使って、表現しようと以下のようなクエリを考えたのですが…
SELECT
`birthday`
, `gradeId`
, `gradeName`
FROM
`tbl_user`
LEFT JOIN
`mst_grade`
ON `mst_grade`.`gradeId` = (
IF('04-01' > RIGHT(curdate(), 5), YEAR(curdate()), YEAR(curdate()) + 1) - YEAR(`tbl_user`.`birthday`))
- ('04-01' < RIGHT(`tbl_user`.`birthday`, 5)
- 6
)
MySQLだとダメみたいですね…
シンタックスエラーになるわけでもないのですが、gradeId gradeName が NULL で返ってきてしまいます。なんでだろう。
わかる人、教えて下さい!