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

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

ただいまの
回答率

90.83%

  • MySQL

    5244questions

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

  • SQL

    2107questions

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

  • データベース

    639questions

    データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

特定条件でのselectの方法

受付中

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 524

zico_teratail

score 559

別の質問を解決したのち、その流れで生じた新たな別の疑問です。

以下のようなテーブル定義の時、たとえば「priority=1のspecが1」かつ「priority=2のspecが5」の人の名前を一発で抜き出すクエリは可能でしょうか?

テーブル定義

※以前の質問でKosuke_Shibuyaさんにご提示いただいたテーブル定義をほぼそのまま踏襲しています。

CREATE TABLE IF NOT EXISTS `abilities` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `member_id` int(11) DEFAULT NULL,
  `spec_id` int(11) DEFAULT NULL,
  `priority` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=6 ;

INSERT INTO `abilities` (`id`, `member_id`, `spec_id`, `priority`) VALUES
(1, 1, 1, 1),
(2, 1, 5, 2),
(3, 2, 1, 1),
(4, 2, 3, 2),
(5, 2, 4, 3);



CREATE TABLE IF NOT EXISTS `member` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=3 ;

INSERT INTO `member` (`id`, `name`) VALUES
(1, 'tanaka'),
(2, 'suzuki');



CREATE TABLE IF NOT EXISTS `spec` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `spec` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=5 ;

INSERT INTO `spec` (`id`, `spec`) VALUES
(1, '走る'),
(2, '投げる'),
(3, '跳ぶ'),
(4, '握る'),
(5, '歩く');

試したクエリ

「id1 tanaka」を抽出したくて以下のクエリを投げましたが、結果は空でした。

SELECT m.id, m.name, GROUP_CONCAT( s.spec
ORDER BY a.priority ) AS specs
FROM member m
LEFT JOIN abilities a ON m.id = a.member_id
LEFT JOIN spec s ON a.spec_id = s.id
WHERE (
a.spec_id =1
AND a.priority =1
)
AND (
a.spec_id =5
AND a.priority =2
)
GROUP BY m.id

このようなwhere句の書き方はダメなのでしょうか。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • A-pZ

    2017/01/05 23:05

    abilitiesにデータ投入している内容を拝見しましたが、「priority=2のspecが5」が1件もありませんね。(逆なら1行ありますが)

    キャンセル

  • zico_teratail

    2017/01/05 23:11

    すみません、誤っていたので修正しました

    キャンセル

回答 4

+3

SELECT m.name
FROM member AS m
INNER JOIN abilities AS a1 ON m.id = a1.member_id
INNER JOIN abilities AS a2 ON a1.member_id = a2.member_id
WHERE a1.priority = 1 AND a1.spec_id = 1
  AND a2.priority = 2 AND a2.spec_id = 5;


http://sqlfiddle.com/#!9/13f74/2

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/01/06 10:19

    あぁ! こんなやり方があったんですね!
    同じテーブルに別名つけて複数回JOINできるなんて知らなかったし思いつきもしなかったです・・・

    ちなみに、spec_idがまだ分からない状態でもクエリ一発で出来ますか?
    たとえば
    「priority=1のspecが“走る”」かつ「priority=2のspecが“歩く”」の人を抽出
    という場合、このクエリをどう拡張したらいいのでしょうか。

    キャンセル

  • 2017/01/06 10:29

    まずはご自分で試行錯誤してみてください。

    2つの abilities テーブル(a1 と a2)それぞれに spec テーブルを INNER JOIN してやれば、できるはずです。

    キャンセル

+1

やりたいことを突貫工事で作ると、
下記のように改修したら恐らくいけるのではないかなと。
※動作確認まではできてません

ただし、優先順位に欠番があったら多分死にます。
(Kosuke_Shibuyaさんのクエリを割と流用してます、すみませんm(__)m)

-- 優先順位に欠番があったら死ぬ
SELECT
    m.id
,   m.name
-- ,   t.spec_id_list
,   t.specs
FROM
    member m 
    LEFT JOIN (
        SELECT
            member_id
        ,   GROUP_CONCAT(a.spec_id ORDER BY a.priority SEPARATOR '') AS spec_id_list
        ,   GROUP_CONCAT(s.spec ORDER BY a.priority) AS specs
        FROM
            abilities a
        LEFT JOIN spec s 
          ON a.spec_id = s.id 
        GROUP BY
            member_id
    ) t
      ON m.id = t.member_id
WHERE
    -- 1番得意なことがスペック1番
    SUBSTRING(t.spec_id_list, 1, 1) = '1'
    -- 2番目に得意なことがスペック5番
AND SUBSTRING(t.spec_id_list, 2, 1) = '5'

後、MySQLではとりあえず動くので意識されてないのかもしれませんが、
SQLでGROUP BY句を利用した場合、
原則としてSELECT句に記載できるものは以下となるので気をつけましょう(他のDBに移った時にハマります)

  1. GROUP BYに指定したカラム
  2. 集約関数を適用したもの(集約関数内ではGROUP BY未指定カラムを指定可)

つまり質問者様の元々のクエリですと、
m.nameのようにそのまま記載するのは通常アウトで、
MAX(m.name) AS nameなどとするのが正解です。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/01/06 14:19

    ありがとうございます。
    MySQL以外を使うことは過去も未来も無いとは思いますが、原則としてそういう決まりがあることは気をつけたいと思います。

    キャンセル

0

SELECT 
    m.id
    , m.name
    , a.priority
    , s.id
    , GROUP_CONCAT( s.spec ORDER BY a.priority ) AS specs
FROM member m
LEFT JOIN abilities a ON m.id = a.member_id
LEFT JOIN spec s ON a.spec_id = s.id
GROUP BY m.id
HAVING (a.priority = 1 AND s.id = 1) OR (a.priority = 2 AND s.id = 3)

イメージ説明

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/01/06 01:20

    ありがとうございます。
    HAVING句でORが使われておりますが、ここはANDではないのでしょうか?
    「priority=1のspecが1」かつ「priority=2のspecが5」の人をselectしたいので・・・。
    ただ、自分でANDに変更してみたところ、返り値が空でした。

    キャンセル

  • 2017/01/06 01:52 編集

    ANDじゃダメだからORにしてます。
    「priority=1のspecが1」かつ「priority=2のspecが5」の人ってそもそも条件を満たすレコードは存在しないので。

    ↑この条件というのは、男性でかつ女性の人を検索するということと同じです。この場合、「かつ」ではなく、「または」です。

    キャンセル

  • 2017/01/06 08:19

    「priority=1のspecが1」かつ「priority=2のspecが5」の人
    ↑これはmember_id=1のtanakaさんが該当します↓
    (1, 1, 1, 1),
    (2, 1, 5, 2),


    >この条件というのは、男性でかつ女性の人を検索するということと同じ

    いえ、そうではなくて、
    「1番得意なことがスペック1番」で、なおかつ「2番目に得意なことがスペック5番」である人を検索したいのです。

    キャンセル

  • 2017/01/06 13:59

    回答はそうなっています。
    あなたの提示した条件 AND でつなくということが 男性かつ女性という矛盾した条件になっています。

    キャンセル

  • 2017/01/06 14:17

    ???

    キャンセル

  • 2017/01/06 14:26

    こっちのクエリの方が伝わりやすいかな

    SELECT
    m.id
    , m.name
    , a.priority
    , count(s.id)
    , GROUP_CONCAT( a.priority ORDER BY a.priority ) AS priority
    , GROUP_CONCAT( s.id ORDER BY a.priority ) AS specs
    FROM member m
    LEFT JOIN abilities a ON m.id = a.member_id
    LEFT JOIN spec s ON a.spec_id = s.id
    WHERE 1
    AND (a.priority = 1 AND s.id = 1)
    OR (a.priority = 2 AND s.id = 5)
    GROUP BY m.id
    HAVING count(s.id) = 2

    キャンセル

  • 2017/01/06 14:44

    ありがとうございます。
    そのクエリを試してみましたが、返り値が空でした。

    キャンセル

  • 2017/01/08 04:14 編集

    基本的に自分の回答は検証済みです。
    データに差異があるのでは?

    キャンセル

  • 2017/01/09 13:37

    失礼しました。
    再度検証したところ、希望の結果が得られました。

    http://sqlfiddle.com/#!9/13f74/6/0

    キャンセル

0

EXISTS句で他のテーブル検索してabilityテーブルから両方が見つかったmemberだけリストすればいいかな

SELECT *
FROM member AS m
WHERE EXISTS(
  SELECT *
  FROM abilities a
  WHERE a.member_id=m.id
    AND a.priority=1
    AND a.spec_id=1)
  AND EXISTS(
  SELECT * FROM abilities a
  WHERE a.member_id=m.id
    AND a.priority=2
    AND a.spec_id=5)

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

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

  • ただいまの回答率 90.83%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

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

  • MySQL

    5244questions

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

  • SQL

    2107questions

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

  • データベース

    639questions

    データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます