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

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

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

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Q&A

解決済

4回答

4437閲覧

mysql where句 複数テーブルで複数条件の摘出

shimpei

総合スコア13

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

0グッド

0クリップ

投稿2017/05/24 08:21

編集2017/05/24 09:20

お世話になります。
mysqlを二日前から勉強し始めた超初心者です。
まったくの初心者なのですが、自力での解決が難しそうなので、教えて下さい。

<現状の問題点>

後述のSQLのWhere句の条件に沿って、テーブル①から2レコード目だけを抽出したいのですが、
想定通りの結果になりません。
下記の「実現したい実行結果」を表示させるやり方を教えて下さい。
よろしくお願い致します。
また、可能であれば、現在のSQLの何が悪いか教えて頂けると幸いです。

※表示させたいレコードの条件について
下記2つの条件を満たすレコードを抽出したいです。
・条件①:
「mgmt_server_backup」テーブルから、下記の有効期限切れのデータを抽出する。
(job_status=0、lock_flg=0、del_flg=0、expire_date is not null、expire_date<now)

・条件②:
上記で抽出したレコードの中で、共有されていないレコードを抽出する。
「mgmt_server_backup」テーブルの「mgmt_server_backup_id」と、
「mgmt_server_backup_share」テーブルの「mgmt_server_backup_id」が一致しない。

元々のSQLは条件①のみの以下だったので、できれば、少ない修正で実現できればと考えております。
■元々のSQL(現行)

MySQL

1select 2 mgmt_server_backup_id as mgmtServerBackupId, 3 mst_customer_code as mstCustomerCode 4from 5 mgmt_server_backup 6where 7 job_status = 0 and 8 lock_flg = 0 and 9 del_flg = 0 and 10 expire_date is not null and 11 expire_date >= NOW(); 12

■現在の実行結果
|mgmtServerBackupId | mstCustomerCode|
|:--|:--:|--:|
|11|10000001|
|22|3|
|22|3|

■実現したい実行結果
|mgmtServerBackupId | mstCustomerCode|
|:--|:--:|--:|
|22|3|

<実行環境情報>
以下の2つのテーブルがあります。
■テーブル①:mgmt_server_backup
|mgmt_server_backup_id|mst_customer_code|job_status|lock_flg | del_flg | expire_date|
|:--|:--:|--:|
|11|10000001|0|0|0|2020/05/24|
|22|3|0|0|0|2020/05/24|

■テーブル②:mgmt_server_backup_share
|mgmt_server_backup_id|del_flg|
|:--|:--:|--:|
|11|0|
|33|0|

■実行SQL

MySQL

1select 2 msb.mgmt_server_backup_id as mgmtServerBackupId, 3 msb.mst_customer_code as mstCustomerCode 4from 5 mgmt_server_backup AS msb, mgmt_server_backup_share AS msbs 6where 7 msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id and 8 msbs.del_flg = 0 and 9 msb.job_status = 0 and 10 msb.lock_flg = 0 and 11 msb.del_flg = 0 and 12 msb.expire_date is not null and 13 msb.expire_date >= NOW() 14;

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

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

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

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

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

hatena19

2017/05/24 08:30

表は表タグ、コードはコードタグで囲んでください。上のツールバーで入力できます。Markdown記法です。
KiyoshiMotoki

2017/05/24 08:45

「表示させたいレコードの条件」を、文章でも記述してください。現状の SQL文で意図する結果が得られないということは、「SQLのWhere句の条件」自体に誤りがある可能性もあるからです。
KiyoshiMotoki

2017/05/24 09:42

shimpei様。情報の追記、ありがとうございます。回答を記載しましたので、ご確認ください。
guest

回答4

0

ベストアンサー

sql

1select 2 msb.mgmt_server_backup_id as mgmtServerBackupId, 3 msb.mst_customer_code as mstCustomerCode 4from 5 mgmt_server_backup AS msb 6left outer join 7 mgmt_server_backup_share AS msbs ON msb.mgmt_server_backup_id = msbs.mgmt_server_backup_id 8where 9 msb.job_status = 0 and 10 msb.lock_flg = 0 and 11 msb.del_flg = 0 and 12 msb.expire_date is not null and 13 msb.expire_date < NOW() and 14 msbs.mgmt_server_backup_id is null;

実行結果
http://sqlfiddle.com/#!9/140fbb/1


可能であれば、現在のSQLの何が悪いか教えて頂けると幸いです。

については、mgmt_server_backupテーブルとmgmt_server_backup_shareの結合条件に問題があります。

質問欄にご提示の

sql

1from 2 mgmt_server_backup AS msb, mgmt_server_backup_share AS msbs 3where 4 msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id and

という結合方法は
mgmt_server_backup_idが互いに異なるレコード同士を結合する」
という意味になります。

これは互いに無関係なレコード同士を結合しているだけですので、ここから
mgmt_server_backupテーブルに存在し、
mgmt_server_backup_shareテーブルには存在しないmgmt_server_backup_id
をうまく抽出することはできません。

意図する結果を得るためには、私の回答の

sql

1from 2 mgmt_server_backup AS msb 3left outer join 4 mgmt_server_backup_share AS msbs ON msb.mgmt_server_backup_id = msbs.mgmt_server_backup_id

部分で
「同じmgmt_server_backup_idを持つレコードを左外部結合」
し、その上で

sql

1msbs.mgmt_server_backup_id is null

という WHERE条件により、
「結合条件を満たすmgmt_server_backup_shareテーブルのレコードが存在しなかった
mgmt_server_backupテーブルのレコード」
を絞り込む必要がありました。

投稿2017/05/24 09:41

編集2017/05/24 12:19
KiyoshiMotoki

総合スコア4791

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

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

hatena19

2017/05/24 09:50

サブクエリを使わなても、外部契合してwhere条件だけでできるのですね。私のよりこっちの方がスッキリしていいですね。
shimpei

2017/05/24 11:04

丁寧な回答ありがとうございます。 「msbs.mgmt_server_backup_id is null」について、理解するのに時間がかかりましたが、内容を理解することができました。 本当にありがとうございました!
hatena19

2017/05/24 11:07

よくみたら、質問の実行SQLにある msbs.del_flg = 0 が抜けてますね。もし、mgmt_server_backup_share に、 (22, 1) というレコードがあった場合、抽出されないですね。 http://sqlfiddle.com/#!9/4d329/8 かな?
KiyoshiMotoki

2017/05/24 12:26

shimpei様 解決できたようで、何よりです。 > 現在のSQLの何が悪いか について若干、追記しましたので、興味があれば確認してみてください。 hatena19様 コメント及び補足、ありがとうございます。 msbs.del_flg = 0 が「本来、意図する抽出条件」にどのように関わってくるか不明だったため、 除外させていただきました。 当カラムが論理削除を意味するものであれば、ご提示いただいた SQL文が正解となりますね。
guest

0

情報があまりに少ないので、憶測を交えてとりあえず再現してみました。

SQL

1CREATE TABLE `mgmt_server_backup` ( 2 `mgmt_server_backup_id` int(11) NOT NULL, 3 `mst_customer_code` varchar(8) NOT NULL, 4 `job_status` int(11) NOT NULL, 5 `lock_flg` tinyint(1) NOT NULL, 6 `del_flg` tinyint(1) NOT NULL, 7 `expire_date` date DEFAULT NULL 8) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 9 10INSERT INTO `mgmt_server_backup` (`mgmt_server_backup_id`, `mst_customer_code`, `job_status`, `lock_flg`, `del_flg`, `expire_date`) VALUES 11(11, '10000001', 0, 0, 0, '2020-05-24'), 12(22, '3', 0, 0, 0, '2020-05-24'); 13 14CREATE TABLE `mgmt_server_backup_share` ( 15 `mgmt_server_backup_id` int(11) NOT NULL, 16 `del_flg` tinyint(1) NOT NULL 17) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 18 19INSERT INTO `mgmt_server_backup_share` (`mgmt_server_backup_id`, `del_flg`) VALUES 20(11, 0), 21(33, 0); 22

ここまでで違うところがあれば教えてください。

次に、問題のSQLを実行してみます。

SQL

1SELECT 2 msb.mgmt_server_backup_id AS mgmtServerBackupId, 3 msb.mst_customer_code AS mstCustomerCode 4FROM 5 mgmt_server_backup AS msb, 6 mgmt_server_backup_share AS msbs 7WHERE 8 msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id 9 AND msbs.del_flg = 0 10 AND msb.job_status = 0 11 AND msb.lock_flg = 0 12 AND msb.del_flg = 0 13 AND msb.expire_date IS NOT NULL 14 AND msb.expire_date >= NOW();
mgmtServerBackupIdmstCustomerCode
223
1110000001
223

ここまで再現できました。

さて、検証するにあたり、SELECTを一旦全選択にしてみます。
また、条件も外してしまいましょう。

SQL

1SELECT 2 * 3FROM 4 mgmt_server_backup AS msb, 5 mgmt_server_backup_share AS msbs 6WHERE 7 1 = 1;
mgmt_server_backup_idmst_customer_codejob_statuslock_flgdel_flgexpire_datemgmt_server_backup_iddel_flg
11100000010002020-05-24110
2230002020-05-24110
11100000010002020-05-24330
2230002020-05-24330

この結果を見る限り、以下の WHERE句のみで絞り込みが実施されていることがわかります。

SQL

1msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id

他の部分は、必要ないわけではありませんが、今回のレコードでは関係ありません。
すべてのレコードが該当するからです。

SQL

1 AND msbs.del_flg = 0 2 AND msb.job_status = 0 3 AND msb.lock_flg = 0 4 AND msb.del_flg = 0 5 AND msb.expire_date IS NOT NULL 6 AND msb.expire_date >= NOW();

WHERE句の働きを見たうえで再度、WHEREをかけて実行してみます。

SQL

1SELECT 2 * 3FROM 4 mgmt_server_backup AS msb, 5 mgmt_server_backup_share AS msbs 6WHERE 7 msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id 8 AND msbs.del_flg = 0 9 AND msb.job_status = 0 10 AND msb.lock_flg = 0 11 AND msb.del_flg = 0 12 AND msb.expire_date IS NOT NULL 13 AND msb.expire_date >= NOW(); 14
mgmt_server_backup_idmst_customer_codejob_statuslock_flgdel_flgexpire_datemgmt_server_backup_iddel_flg
2230002020-05-24110
11100000010002020-05-24330
2230002020-05-24330

ここからは、shimpei さんがどのような値を取りたいのかによるのでなんとも言えませんが、ここも憶測で以下のように定義してみます。

mgmtServerBackupId と mstCustomerCode の組み合わせが一致するレコードが複数ある場合

mgmtServerBackupId と mstCustomerCode の組み合わせが一致する ということは、mgmtServerBackupId と mstCustomerCode の組み合わせでグループ化することで、実現することが出来ます。
グループ化して、該当のレコードがいくつあるかをカウントする処理まで書いてみましょう。

SQL

1SELECT 2 msb.mgmt_server_backup_id AS mgmtServerBackupId, 3 msb.mst_customer_code AS mstCustomerCode, 4 COUNT(*) 5FROM 6 mgmt_server_backup AS msb, 7 mgmt_server_backup_share AS msbs 8WHERE 9 msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id 10 AND msbs.del_flg = 0 11 AND msb.job_status = 0 12 AND msb.lock_flg = 0 13 AND msb.del_flg = 0 14 AND msb.expire_date IS NOT NULL 15 AND msb.expire_date >= NOW() 16GROUP BY 17 msb.mgmt_server_backup_id, msb.mst_customer_code;
mgmtServerBackupIdmstCustomerCodeCOUNT(*)
11100000011
2232

一つポイントとして、GROUP BY は WHERE の後に実行されます。
ですので、その結果を WHERE として指定することは出来ません。
この場合は HAVING 句を使います。

SQL

1SELECT 2 msb.mgmt_server_backup_id AS mgmtServerBackupId, 3 msb.mst_customer_code AS mstCustomerCode 4FROM 5 mgmt_server_backup AS msb, 6 mgmt_server_backup_share AS msbs 7WHERE 8 msb.mgmt_server_backup_id <> msbs.mgmt_server_backup_id 9 AND msbs.del_flg = 0 10 AND msb.job_status = 0 11 AND msb.lock_flg = 0 12 AND msb.del_flg = 0 13 AND msb.expire_date IS NOT NULL 14 AND msb.expire_date >= NOW() 15GROUP BY 16 msb.mgmt_server_backup_id, msb.mst_customer_code 17HAVING 18 COUNT(*) > 1;
mgmtServerBackupIdmstCustomerCode
223

無事に、最初の条件を満たす式がかけました。

ちなみに、”msb.expire_date >= NOW()” を指定している時点でNULLは除かれるので ”msb.expire_date IS NOT NULL” は必要ありません。

また、今回のSQL文は説明重視で書いているため、とても実用的ではありません。
あくまで参考としてください。

投稿2017/05/24 09:46

tomcat0090

総合スコア66

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

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

shimpei

2017/05/24 10:20

丁寧な回答ありがとうございます。 >ここからは、shimpei さんがどのような値を取りたいのかによるのでなんとも言えませんが 今回の変更は、上記質問に追記した、条件②になるので、2つのテーブルの「mgmt_server_backup_id」が一致してないレコードを抽出したいのです。 なので、Group byでまとめないで、「mgmtServerBackupId」が「22」のレコード1件が抽出できると考えています。この方法を教えて頂けないでしょうか?質問が下手で申し訳ありません。 ・条件②: 上記で抽出したレコードの中で、共有されていないレコードを抽出する。 「mgmt_server_backup」テーブルの「mgmt_server_backup_id」と、 「mgmt_server_backup_share」テーブルの「mgmt_server_backup_id」が一致しない。
tomcat0090

2017/05/24 13:15

ごめんなさい、回答中に追記がされていたのを見ていませんでした^^; 解決したようで何よりです!
guest

0

条件がいまいち不明瞭ですが、下記のようなことかな?

SQL

1select 2msb.mgmt_server_backup_id as mgmtServerBackupId, 3msb.mst_customer_code as mstCustomerCode 4from 5 (select * from mgmt_server_backup 6 where 7 job_status = 0 and 8 lock_flg = 0 and 9 del_flg = 0 and 10 expire_date >= NOW() 11 ) as msb 12 left join 13 (select * from mgmt_server_backup_share 14 where 15 del_flg = 0 16 ) as msbs 17on 18 msb.mgmt_server_backup_id = msbs.mgmt_server_backup_id 19where 20 msbs.mgmt_server_backup_id is null;

言葉にすると、各テーブルを抽出したもの同士を比較して、テーブル②に存在しないテーブル①のデータを抽出。

追記

元々のSQLは条件①のみの以下だったので、できれば、少ない修正で実現できればと考えております。

SQL

1select 2 mgmt_server_backup_id as mgmtServerBackupId, 3 mst_customer_code as mstCustomerCode 4from 5 mgmt_server_backup 6where 7 job_status = 0 and 8 lock_flg = 0 and 9 del_flg = 0 and 10 expire_date is not null and 11 expire_date >= NOW() and 12 not exsist 13 (select * from mgmt_server_backup_share 14 where 15 del_flg = 0 and 16 mgmt_server_backup_share.mgmt_server_backup_id = mgmt_server_backup_share.mgmt_server_backup_id 17 );

元々のSQLに not exsist 以下を追加しただけです。ただし、下記にもあるように、上のSQLより遅くなる場合が多いです。

MySQLで集合差を出す - Qiita

投稿2017/05/24 08:52

編集2017/05/24 11:27
hatena19

総合スコア33620

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

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

shimpei

2017/05/24 09:31

ご回答頂きありがとうございます。 条件について、下記の記載を追記致しました。 「※表示させたいレコードの条件について ~」 できれば、「元々のSQL(現行)」をあまり修正しない形を望んでおります。大変お手数ですが、よろしくお願いします。
guest

0

LIMIT 1,1をつけて検索してみてはいかがですか?
ちなみにSQLはORDER BY で明示しない限り
表示順は保証されていません。

投稿2017/05/24 08:33

yambejp

総合スコア114572

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

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

shimpei

2017/05/24 09:32

ご回答頂きありがとうございます。 お手数ですが、LIMIT 1,1について、どのように修正すればよろしいでしょうか?
shimpei

2017/05/24 09:43

LIMIT1,1を試した結果、期待した結果になったのですが、これはあくまで、質問するためのテストデータでの話なので、実際に何百件もある場合を考えると、正式には難しいと考えています。 お手数をお掛けしますが、何か他にいいやり方はご存じないでしょうか?
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問