LoginSignup
3

More than 5 years have passed since last update.

SQLで誕生日から現在の学年を取得する

Last updated at Posted at 2015-03-09

前提条件として、以下の二つのテーブルを持っています。

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年生であるということです。

では、誕生日から年齢を算出することができれば、応用できそうですので、ググると…

MySQL で誕生日のカラムから年齢を計算する方法

-- 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 で返ってきてしまいます。なんでだろう。
わかる人、教えて下さい!

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3