Help us understand the problem. What is going on with this article?

【SQL腕試し問題】現在の部署に所属する社員の一覧を取得するSQLを書いて下さい

More than 1 year has passed since last update.

はじめに

結構よくあるパターンなので知っている人は知っていると思いますが、初めて見た人は「???」となりそうな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ができたらgistSQL FiddleのURLをこの記事のコメントに貼り付けて下さい。工夫した点やアピールポイントがあればご自由にどうぞ。

Screen Shot 2014-01-24 at 5.58.31.png

その他

  • 優勝者には素敵な商品が・・・出ません。順位を付ける予定もありません。各自、自己満足の世界でSQLを作って下さい。
  • バリエーションとしてよくあるのが、所属履歴に開始日付と終了日付を持っていて未来の異動予定も予め入力できるパターンです。こちらのSQLも考えてみると勉強になるかもしれません。

僕の解答例

僕はこんな感じで作ってみました。
この手の解答例としては比較的スタンダードだと思うのですが、どうなんでしょう?
あ、自力で答えを考えたい人はまだ見ないで下さいね!!

最後に

それではみなさん、Let's try!!

過去に作ったSQL腕試し問題

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした