DataBase,SQL/SQL文あわせ技 - asamiのメモ帳

LEFT OUTER JOIN　と　文字列連結

【データ】
(1)部署マスタテーブル(busho)

• 部署ＩＤ(busho_id) - NOT NULL
• 部署名(busho_name) - NOT NULL

(2)社員マスタテーブル(shain)

• 社員ＩＤ(shain_id) - NOT NULL
• 社員名(shain_name) - NOT NULL

(3)所属部署テーブル(shozoku)

• 部署ＩＤ(busho_id) - NOT NULL
• 社員ＩＤ(shain_id) - NOT NULL

(4)基本連絡先テーブル(renraku)

• 社員ＩＤ(shain_id) - NOT NULL
• メールアドレス(email) - NOT NULL
• メールアドレス2(email2) - NOT NULL

(5)緊急連絡先テーブル(kinkyu_renraku)

• 社員ＩＤ(shain_id) - NOT NULL
• メールアドレス(email) - NOT NULL
• メールアドレス2(email2) - NOT NULL

【やりたいこと】
部署名、社員名、基本連絡先、緊急連絡先を取得する。
基本連絡先、緊急連絡先は、メールアドレスが複数登録されている場合、":"で区切って一つの項目として取得する。

【SQL】
SELECT
　shain.shain_name,
　busho.busho_name,
　case renraku.email2 '' then renraku.email else renraku.email || ':' || renraku.email2 end as renraku_email,
　case kinkyu_renraku.email2 '' then kinkyu_renraku.email else kinkyu_renraku.email || ':' || kinkyu_renraku.email2 end as kinkyu_email
FROM
　busho,
　shozoku,
　shain
　　left outer join renraku on (shain.shain_id = renraku.shain_id)
　　left outer join kinkyu_renraku on (shain.shain_id = kinkyu_renraku.shain_id)
WHERE
　shain.shain_id = shozoku.shain_id AND
　shozoku.busho_id = busho.busho_id AND
ORDER BY
　busho.busho_id asc,
　shain.shain_id asc

【解説】
LEFT OUTER JOIN
shain　left outer join renraku on (shain.shain_id = renraku.shain_id)
基本連絡先テーブルに該当する社員の情報がなかったら、検索結果０件にするのではなく、値にNULLを設定する。

CASE文　と　文字連結
case kinkyu_renraku.email2 '' then kinkyu_renraku.email else kinkyu_renraku.email || ':' || kinkyu_renraku.email2 end as kinkyu_email
緊急連絡先のメールアドレス2が空文字だったらメールアドレス1だけを表示する。
それ以外だった場合は、緊急連絡先メールアドレス1と緊急連絡先メールアドレス2を":"でつないで表示する。

---

INSERTするときに値に改行コードを入れたい

【SQL文】
(1)INSERT INTO a values (0000, 'あい' || chr(13) || chr(10) || 'うえお');
(2)INSERT INTO a values (0001, 'かき' || chr(10) || 'くけこ');

【解説】
(1)
aテーブルの2つ目の項目を「あい\r\nうえお」で登録
(2)
aテーブルの2つ目の項目を「かき\nくけこ」で登録

---

システム日付の月だけを取得したい。(Oracle)

例)AAAテーブルのMONTHというカラムを今の月に更新したい。
UPDATE AAA SET MONTH = TO_CHAR(SYSDATE, 'MM');

結果)
今日の日付:2010/06/16

AAAテーブル

MONTH

6

合計： -