質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

3回答

2974閲覧

SQLで同一カラムに2つ以上のIDが入っている場合のJOIN方法

DJR

総合スコア13

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2019/03/15 06:31

一つのカラムに1〜3個のIDが入っておりますが、JOINの仕方が分からないのでお教えいただけますか?
実際のテーブルが以下の表です。

イメージ説明

また、想定している出力が下記の2つです。
もともとのbaseballclubというテーブルの出身小学校をコードから学校名に置換したいイメージです。

イメージ説明

ご存知の方、SQLまたはヒントのご教示お願いします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

maisumakun

2019/03/15 06:33

「一つのカラムに1〜3個のIDが入って」いるデータ構造を変える、という選択肢はありますか?
DJR

2019/03/15 07:03

ご回答ありがとうございます。これを私がいじる権限はないので今回はこの選択肢を外していただけるとありがたいです。
guest

回答3

0

ベストアンサー

データ内容から出身小学校は配列の様ですね。
配列は、**項目名[添字]**で参照できます。

SQL

1select club.名前,club.年齢, elmnt.学校名 2from baseballclub club 3 left join elementary elmnt 4 on club.出身小学校[1]=elmnt.コード

ただ、上記だと出身小学校出身小学校[1]の式インデックスを追加しないと遅いかもしれません。

因みに、配列演算子を用いて結合した場合は、

SQL

1select club.名前,club.年齢, (array_agg(学校名))[1] as 学校名 2from baseballclub club 3 left join elementary elmnt 4 on club.出身小学校 @> array[elmnt.コード] 5group by club.名前,club.年齢

但し、学校名を出身小学校の1番目にするには以下の様にもう少し工夫が必要です。

SQL

1select club.名前, club.年齢, (array_agg(学校名 order by 並び))[1] 2from ( 3 select * 4 from baseballclub, unnest(出身小学校) with ordinality as w(出身小学校コード, 並び) 5 ) club 6 left join elementary elmnt 7 on club.出身小学校 @> array[elmnt.コード] 8group by club.名前, club.年齢

出身小学校が展開された形での取得は、

SQL

1select club.名前, club.年齢, elmnt.学校名 2from baseballclub club 3 left join elementary elmnt 4 on club.出身小学校 @> array[elmnt.コード]

投稿2019/03/15 07:27

編集2019/03/15 08:10
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

DJR

2019/03/15 12:12

ありがとうございます! 最初の 項目名[添字] というやり方があるのですね。 以下のやりかたは少し勉強してやってみます。
guest

0

3つまでと決まっているのであれば、baseballclubテーブルに「出身小学校1」「出身小学校2」「出身小学校3」をもたせる形にしてはどうでしょうか?

3つまでではなく、いくつ入るのか決まっていない場合は、「出身小学校テーブル」を作るべきですよね。
「名前」と「小学校コード」だけを持ったテーブル。
必要なら「シーケンス」とか「入学順」等の列も作って、どの順で小学校に所属していたのかを見れるようにしても。

そもそも、現状のデータの持たせ方は非常に良くないです。

投稿2019/03/15 06:40

kariya

総合スコア75

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

DJR

2019/03/15 07:06

はい。私もそう思いますが、私およびチームにテーブルをいじる権限がありませんし、ベンダーさんが好意で作ってくれている(正式発注していない)ものですので、このカラムを分解するのは現段階では困難なのです…。
kariya

2019/03/15 07:10

テーブルの実体を変更できなくても、SELECT句の中で「出身小学校」の内容を区切り文字等で分解して、3つのカラムを生成してやったものを使えばいいと思いますが、それも難しいですか?
DJR

2019/03/15 07:14

select 文は自由に私が記述できるので問題ありません。 SELECT内で分解して再生成するイメージでしょうか?
kariya

2019/03/15 07:35

SQLServerですし、かなり泥臭い手法なのかもしれませんが。。。 declare @baseballclub table ( name varchar(10) , years int , gakko_cd varchar(20) ); declare @gakko table ( gakko_cd int , gakko_name varchar(20) ); insert @baseballclub values ('辰吉', 12 , '[3]'); insert @baseballclub values ('吉本', 13 , '[3,4]'); insert @baseballclub values ('本山', 14 , '[5,3]'); insert @baseballclub values ('山中', 13 , '[1]'); insert @gakko values (1, '東京北'); insert @gakko values (2, '東京東'); insert @gakko values (3, '東京南'); insert @gakko values (4, '東京西'); insert @gakko values (5, '東京中央'); select y.name , y.years , y.gakko_1 , case when y.gak = '' then '' ELSE left(y.gak, CHARINDEX(',', y.gak) - 1) END as gakko_2 , case when CHARINDEX(',', y.gak) < LEN(y.gak) then replace(right(y.gak, LEN(y.gak) - CHARINDEX(',', y.gak)), ',', '') else '' end as gakko_3 FROM ( select x.name , x.years , left(x.gak, CHARINDEX(',', x.gak) - 1) as gakko_1 , case when CHARINDEX(',', x.gak) < LEN(x.gak) then right(x.gak, LEN(x.gak) - CHARINDEX(',', x.gak)) else '' end as gak from ( select b.name , b.years , REPLACE(REPLACE(b.gakko_cd, '[', ''), ']', '') + ',' as gak from @baseballclub as b ) as x ) as y
sazi

2019/03/15 07:40

postgresのようですが
DJR

2019/03/15 12:08

ありがとうございます。postgreなのですが考え方として参考になります。
sazi

2019/03/16 03:56

@kariyaさん postgresと分かった上なので、コメントにしたという事ですね。 失礼しました。
guest

0

親の仕事により頻繁に引っ越す人もいます。出身小学校が4つ以上の人が入ってきたらどうしますか?
テーブルはデータベースの正規化 に沿って設計します。通常、第3正規化まで行います。第1正規化で繰り返し(今回は出身小学校)を排除します。

投稿2019/03/15 11:42

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

DJR

2019/03/15 12:14

ありがとうございます。テーブル自体を変更する権限がなく困っておりました。自分が設計する場合は上記参考にさせていただきます。
Orlofsky

2019/03/15 12:28

最初にテーブル設計した人が正規化を知らない程度の能力なだけでは? ○○小学校出身者のリストが欲しい、って言われたら今のテーブルで書くSQLと正規化したテーブルのSQLを比べては?
sazi

2019/03/16 04:09 編集

@Orlofskyさん 配列を扱う関数等が充実しているpostgresでは、正規化したものを配列として畳むといった感覚です。 正規化しない分キー部分にかかる容量や結合によるコストが低減されるといったメリットもあります。 実際、正規化した状態の1億件の親に対する8億件の子テーブルを配列にする事で、恩恵を受けたことがあります。 配列を扱う事に充実していないDBMSだったら正規化優先ですけどね。
Orlofsky

2019/03/16 13:40

saziさん、説明ありがとうございます。汎用機のCOBOLからOracleに移行する時、配列(COBOLではテーブルといふ、繰り返し数は固定)をそのままOracleに持って来たい、って要望が必ずと言っていいほどあって、SELECT SUM ... GROUP BY ... の使い方を説明して、しつこい場合は簡単なサンプルSQLやLOOPが必要な時はPL/SQLを書いて処理時間を比較して納得してもらっています。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問