asamiのメモ帳

DataBase,SQL/SQL文あわせ技


※上記の広告は60日以上更新のないWIKIに表示されています。更新することで広告が下部へ移動します。

LEFT OUTER JOIN と 文字列連結


【データ】
(1)部署マスタテーブル(busho)
  • 部署ID(busho_id) - NOT NULL
  • 部署名(busho_name) - NOT NULL

(2)社員マスタテーブル(shain)
  • 社員ID(shain_id) - NOT NULL
  • 社員名(shain_name) - NOT NULL

(3)所属部署テーブル(shozoku)
  • 部署ID(busho_id) - NOT NULL
  • 社員ID(shain_id) - NOT NULL

(4)基本連絡先テーブル(renraku)
  • 社員ID(shain_id) - NOT NULL
  • メールアドレス(email) - NOT NULL
  • メールアドレス2(email2) - NOT NULL

(5)緊急連絡先テーブル(kinkyu_renraku)
  • 社員ID(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)
基本連絡先テーブルに該当する社員の情報がなかったら、検索結果0件にするのではなく、値に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

合計: -