MySQL で学級委員を選出する... それも全クラスで
2010-01-23 15:36:19
ある学校の、生徒の名前と、その所属クラスを記入した、以下のようなテーブルがあるとします。なお、これらのデータは擬似個人情報データ生成サービスにより出力されたもので、全て架空です。
mysql> SELECT * FROM students; +-----+---------------------+---------------------------------+---------------------+----------+ | id | name | kana | roman | class_id | +-----+---------------------+---------------------------------+---------------------+----------+ | 1 | 大森 比呂美 | オオモリ ヒロミ | Oomori Hiromi | 1 | | 2 | 長野 紗矢 | ナガノ サヤ | Nagano Saya | 1 | | 3 | 杉浦 円 | スギウラ マドカ | Sugiura Madoka | 1 | ~~~ | 198 | 寺本 禎 | テラモト タダシ | Teramoto Tadashi | 5 | | 199 | 高橋 満雄 | タカハシ ミツオ | Takahashi Mitsuo | 5 | | 200 | 柴山 謙一 | シバヤマ ケンイチ | Shibayama Kenichi | 5 | +-----+---------------------+---------------------------------+---------------------+----------+ 200 rows in set (0.00 sec)
5 クラスあり、どのクラスも 40 名、総勢 200 名の学校です。
さて、それぞれのクラスから 1 名ずつ、学級委員長を選出するとします。当然立候補者はおらず、推薦のなすりつけ合いになることでしょう。そこで、全クラスとも、抽選で学級委員を選出することになりました。
それぞれのクラスでクジを作るのもいい考えですが、我々エンジニアは、MySQL によるソリューションを行うべきでしょう。というわけで以下の SQL を実行。
SELECT class_id, SUBSTRING_INDEX(GROUP_CONCAT(name ORDER BY RAND()), ',', 1) AS name FROM students GROUP BY class_id
この SQL から得られる結果は以下の通り。
+----------+---------------+ | class_id | name | +----------+---------------+ | 1 | 戸田 有沙 | | 2 | 大森 一行 | | 3 | 金山 清志 | | 4 | 藤木 啓一 | | 5 | 岩渕 雅彦 | +----------+---------------+ 5 rows in set (0.00 sec)
そして、この SQL は、常にランダムな抽出を行うので、当然結果は毎回異なります。例えば以下のように。
+----------+---------------+ | class_id | name | +----------+---------------+ | 1 | 高見 幸子 | | 2 | 森川 里菜 | | 3 | 塩崎 凛乃 | | 4 | 高畑 栄三 | | 5 | 柴山 謙一 | +----------+---------------+ 5 rows in set (0.00 sec)
また、選出された学級委員の名前だけではなく、生徒番号やフリガナ等の情報も紐付ける必要がある場合は、以下のように少々複雑になります。自己結合を使います。
SELECT
sub.class_id,
sub.student_id,
students.name,
students.kana,
students.roman
FROM
(
SELECT
class_id,
SUBSTRING_INDEX(GROUP_CONCAT(id ORDER BY RAND()), ',', 1) AS student_id
FROM
students
GROUP BY
class_id
) AS sub
LEFT JOIN
students
ON
sub.student_id = students.id
+----------+------------+------------------+------------------------------+-------------------+ | class_id | student_id | name | kana | roman | +----------+------------+------------------+------------------------------+-------------------+ | 1 | 32 | 湯川 信明 | ユカワ ノブアキ | Yukawa Nobuaki | | 2 | 78 | 金丸 紗良 | カナマル サラ | Kanamaru Sara | | 3 | 111 | 熊倉 美千子 | クマクラ ミチコ | Kumakura Michiko | | 4 | 150 | 吉田 長次郎 | ヨシダ チョウジロウ | Yoshida Choujirou | | 5 | 167 | 日比野 善之 | ヒビノ ヨシユキ | Hibino Yoshiyuki | +----------+------------+------------------+------------------------------+-------------------+ 5 rows in set (0.00 sec)
この抽選作業を MySQL で実現する上で、あまり日の当たらない (と、少なくとも私は考えている) 関数が 2 つほど活躍しています。それらについてはまたの機会に紹介しようと思っています。こんな、MySQL クックブックの隅にも載らないような SQL は何人の役に立つかはわかりませんが、GROUP_CONCAT は意外とできる子なので、皆さんも是非使ってみてください。