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

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

ただいまの
回答率

89.62%

特定条件でのselectの方法

受付中

回答 4

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,115

zico_teratail

score 467

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

以下のようなテーブル定義の時、たとえば「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 14:44

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

    キャンセル

  • 2017/01/08 03:36 編集

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

    キャンセル

  • 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で質問しよう!

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