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

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

ただいまの
回答率

90.51%

  • SQL

    3006questions

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

【SQL文の書き方について】複数キーワードと複数キーワードを除外した検索

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 925

ssk

score 273

前提・実現したいこと

リレーショナルデータベースを想定しています。
以下のキーワードからSQL文を作成するには、どのような書き方がありますか?
※全てのカラムが検索対象

複数キーワード:「東京 派遣」
除外キーワード:「時給 歩合」

補足情報(言語/FW/ツール等のバージョンなど)

参考書やこちらを参考にSQL文を考えているのですが
カラム全体を対象にしたいので、イメージができません。

サブクエリで除外キーワードを検索してから
さらにキーワード検索する方法が良いと思っています。
(正解かわかりませんが、、、)

何卒、助言の程よろしくお願いいたします。

追記

テーブル構成は以下の通りです。

masterテーブル

id name pre_id em_id
1 会社名1 1 1
2 会社名2 1 3
3 会社名3 2 1

master_formテーブル

id form_id
1 2
2 1
2 2
3 1
3 3

formテーブル

form_id name
1 正社員
2 派遣
3 アルバイト

emテーブル

id name
1 固定給
2 時給
3 歩合

preテーブル

id name
1 東京
2 大阪
3 名古屋
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • A.Ichi

    2017/08/22 13:17

    全てのカラム?であればテーブルの項目は全てcharで構成されている事になりますが・・・

    キャンセル

  • ssk

    2017/08/22 13:54

    検索/除外キーワードの変更とテーブル構成について追記・修正いたしました。また、全てのカラムではなく、idなどの数字を除いたカラムです。言葉足らずで申し訳ございません。

    キャンセル

  • tsuemura

    2017/08/22 15:08

    現在、検索に使用しているSQLを追記してもらえますか?

    キャンセル

回答 3

checkベストアンサー

0

mysqlで作成してみました。

select t1.`name`, t3.`name`, t4.`name`, t5.`name`
from master t1
join master_form t2 on t1.form_id=t2.id
join form        t3 on t2.form_id=t3.form_id
join em          t4 on t1.em_id=t4.id
join pre         t5 on t1.pre_id=t5.id
where t5.`name`='東京'
and   t3.`name`='派遣'
and   t4.`name` not in ('時給','歩合')
;

ERが不明な部分が有り想像して作成しました。
追加

select t1.`name`, t3.`name`, t4.`name`, t5.`name`
from master t1
join master_form t2 on t1.form_id=t2.id
join form        t3 on t2.form_id=t3.form_id and t3.name='派遣'
join em          t4 on t1.em_id=t4.id and t4.`name` not in ('時給','歩合')
join pre         t5 on t1.pre_id=t5.id and t5.`name`='東京'
;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/22 15:01

    SQL文まで、ありがとうございます!
    サブクエリではなく、テーブル結合した後にwhereとnot inを同時に行う方法ですとウエイトが10秒ほどあります。これはしょうがない、、、ということでしょうか?

    キャンセル

  • 2017/08/22 15:04

    もしデータ量が大量であればwhereでの絞りでは、時間が掛かります。分かり易さを優先してみました。ONにて絞れば高速化が可能です。

    キャンセル

  • 2017/08/22 15:09

    おっしゃる通り、データ量が3万ほどあります。
    ONで絞るというと、A.Ichi様が提示してくださったSQL文の場合
    どのようになりますか?

    http://d.hatena.ne.jp/r_ikeda/20090610/outerjoin
    ↑こちらのサイトを見るかぎり、テーブル結合時にONを使うのでしょうか?

    キャンセル

  • 2017/08/22 15:11

    早速、ありがとうございます!><

    キャンセル

  • 2017/08/22 15:12

    サブクエリではなく、joinのタイミングで省いてしまう方法もあるのですね。

    キャンセル

0

フリーワード検索的なものをやろうとしているのでしょうか。

全カラムを結合して、LIKEまたはNOT LIKEで検索するくらいしか思いつきません。
以下はPostgreSQLの場合です。

SELECT
  * 
FROM 
  table1 
WHERE 
  col1 || col2 || col3 ||  ...  || col99 LIKE ('%東京%','%大阪%', '%時給%') AND
  col1 || col2 || col3 ||  ...  || col99 NOT LIKE ('%渋谷区%', '%目黒区%', '%正社員%');


各DBMSごとの文字列結合の方法はhttp://www.sql-reference.com/string/concatenate.htmlを、
ワイルドカード検索はhttp://www.sql-reference.com/select/like.htmlを参考にしてください。

ただ、恐らくパフォーマンス上の問題があると思います。
正規化や全文検索インデックスなど、必要に応じて検討してください。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/22 14:06

    ありがとうございます。

    >>>ただ、恐らくパフォーマンス上の問題があると思います。
    複数テーブルのため、結合してからLIKEとNOT LIKEをやってみたんですが
    ウエイトが10秒程ありまして。。正規化はできているのでサブクエリを絡ませて。と考えていました。

    キャンセル

0

master_formの使い所がイマイチわかりません
masterにあるform_idと整合性がとれていないのでは?

 追記

formはmaster_form経由でmasterとつながるのですね
idやnameなどかぶっているものが多いのでちょっと整理して以下例示します

create table master(m_id int,name varchar(20),p_id int,e_id int);
insert into master values(1,'会社名1',1,1),(2,'会社名2',1,3),(3,'会社名3',2,1);

create table master_form(m_id int,f_id int);
insert into master_form values(1,2),(2,1),(2,2),(3,1),(3,3);

create table form(f_id int,f_name varchar(20));
insert into form values(1,'正社員'),(2,'派遣'),(3,'アルバイト');

create table em(e_id int,e_name varchar(20));
insert into em values(1,'固定給'),(2,'時給'),(3,'歩合');

create table pre(p_id int,p_name varchar(20));
insert into pre values(1,'東京'),(2,'大阪'),(3,'名古屋');


こうであれば、検索は以下になります

select * from master as m
inner join master_form as mf on m.m_id=mf.m_id
inner join form as f on mf.f_id=f.f_id
inner join em as e on m.e_id=e.e_id
inner join pre as p on m.p_id=p.p_id


このデータから複数キーワード:「東京 派遣」、除外キーワード:「時給 歩合」 を
当てはめると結果はどれをほしいのでしょうか?
m_id単位で集計したいのでしょうか?

 調整

インデックスの付け直しおよびexistsをつかってこれでどうでしょう?

create table master(m_id int,name varchar(20),p_id int,e_id int,index(m_id,p_id,e_id));
insert into master values(1,'会社名1',1,1),(2,'会社名2',1,3),(3,'会社名3',2,1);

create table master_form(m_id int,f_id int,index(m_id,f_id));
insert into master_form values(1,2),(2,1),(2,2),(3,1),(3,3);

create table form(f_id int,f_name varchar(20),index(f_id,f_name));
insert into form values(1,'正社員'),(2,'派遣'),(3,'アルバイト');

create table em(e_id int,e_name varchar(20),index(e_id,e_name));
insert into em values(1,'固定給'),(2,'時給'),(3,'歩合');

create table pre(p_id int,p_name varchar(20),index(p_id,p_name));
insert into pre values(1,'東京'),(2,'大阪'),(3,'名古屋');
select * from master as m
where 1
and exists(select m_id from master_form as mf inner join form as f on mf.f_id=f.f_id and f.f_name='派遣' where mf.m_id=m.m_id)
and exists(select e_id from em as e where not e.e_name='時給' and not e_name='歩合' and e.e_id=m.e_id)
and exists(select p_id from pre as p where p.p_name='東京' and p.p_id=m.p_id)

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/22 15:31

    ご指摘ありがごうざいます。
    おっしゃる通りでしたので、テーブルを修正いたしました。

    キャンセル

  • 2017/08/22 15:57

    ありがとうございます。
    m_id単位で集計したいです。

    キャンセル

  • 2017/08/22 16:34

    調整版を追記しておきました

    キャンセル

  • 2017/08/24 13:26

    返信が遅くなってしまい、ありがとうございます。
    existsを利用する方向で進めていきたいと思います。

    キャンセル

同じタグがついた質問を見る

  • SQL

    3006questions

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