はじめに
結構よくあるパターンなので知っている人は知っていると思いますが、初めて見た人は「???」となりそうなSQLの問題です。
SQLの腕試し問題として出題してみますので、みなさん奮って答えを考えてみて下さい！！
要件
- 社員と所属履歴という二つのモデルがある。
- 社員は1件以上の所属履歴を持つ。
- 所属履歴にはその社員がこれまでに所属してきた部署の履歴が保存される。
- 所属履歴には開始日が設定されている。
- 所属履歴が複数ある場合、開始日が最新の履歴が現在の部署を表す。
- 現在IT部門に所属している社員の一覧を取得するにはどうすれば良いか。
例
社員テーブル(employees)
| id | name |
|---|---|
| 1 | John |
| 2 | Mary |
| 3 | Tom |
所属履歴テーブル(section_histories)
| id | employee_id | start_date | section_name} |
|---|---|---|---|
| 1 | 1 | 2013/01/01 | Sales |
| 2 | 2 | 2013/01/01 | IT |
| 3 | 3 | 2013/01/01 | IT |
| 4 | 1 | 2014/01/01 | IT |
| 5 | 2 | 2014/01/01 | Sales |
補足
- John(employee_id: 1)の現在の部署はIT
- Mary(employee_id: 2)の現在の部署はSales
- Tom(employee_id: 3)の現在の部署はIT
期待する出力結果
| employee_id | name | section_name | start_date |
|---|---|---|---|
| 1 | John | IT | 2014/01/01 |
| 3 | Tom | IT | 2013/01/01 |
補足
- ソート順は
employee_idとする
制約等
- 推奨RDBMSはPostgreSQL 9.2.4だが、それ以外のRDBMSでも可。
スキーマ作成用のSQL
CREATE TABLE employees (
id integer PRIMARY KEY
,name varchar(50)
);
INSERT INTO employees VALUES (1,'John');
INSERT INTO employees VALUES (2,'Mary');
INSERT INTO employees VALUES (3,'Tom');
CREATE TABLE section_histories (
id integer PRIMARY KEY
,employee_id integer
,start_date date
,section_name varchar(50)
);
INSERT INTO section_histories VALUES (1,1,'2013/01/01', 'Sales');
INSERT INTO section_histories VALUES (2,2,'2013/01/01', 'IT');
INSERT INTO section_histories VALUES (3,3,'2013/01/01', 'IT');
INSERT INTO section_histories VALUES (4,1,'2014/01/01', 'IT');
INSERT INTO section_histories VALUES (5,2,'2014/01/01', 'Sales');
解答方法
- 実行方法は自由ですが、SQL Fiddleを使うとお手軽かもしれません。
- SQLができたらgistやSQL FiddleのURLをこの記事のコメントに貼り付けて下さい。工夫した点やアピールポイントがあればご自由にどうぞ。
その他
- 優勝者には素敵な商品が・・・出ません。順位を付ける予定もありません。各自、自己満足の世界でSQLを作って下さい。
- バリエーションとしてよくあるのが、所属履歴に開始日付と終了日付を持っていて未来の異動予定も予め入力できるパターンです。こちらのSQLも考えてみると勉強になるかもしれません。
僕の解答例
僕はこんな感じで作ってみました。
この手の解答例としては比較的スタンダードだと思うのですが、どうなんでしょう？
あ、自力で答えを考えたい人はまだ見ないで下さいね！！
- http://sqlfiddle.com/#!12/ae7cf/2
- SQL Fiddleの調子が悪いときはこちらのgistをどうぞ。
最後に
それではみなさん、Let's try!!