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

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

ただいまの
回答率

90.52%

  • MySQL

    5844questions

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

  • SQL

    2386questions

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

SQL文をもっとよくしたい

解決済

回答 2

投稿 編集

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

k499778

score 497

質問があるのですが、
以下のSQLはどのような書き方にすればもっとよくなるでしょうか?

ちなみにORマッパーはDomaを使っています

要件は以下です。
「ログインユーザーが所属する店舗(store_mst)あるいは、そのチェーン店(groupstore_mst)に紐づく商品分類タグ(tag_mst)で、その商品分類タグのWEB表示(tag_mst.display_flg = 1)件数が合わせて5件を超える店舗IDを取得する」

スキーマ構成としては以下です。

商品分類タグマスタ(tag_mst)
・tag_id(PK)
・store_id
・groupstore_id
・display_flg

店舗マスタ(store_mst)
・store_id(PK)
・groupstore_id
・store_name

チェーン店マスタ(groupstore_mst)
・groupstore_id(PK)
・groupstore_name

今SQLで以下ようなクエリを作成しています。Domaの書き方になっています。引数はログインユーザーのstoreIdです。

SELECT
   T5.store_id
FROM
  (
  SELECT
    T1.store_id,
    (
    SELECT COUNT(*)
    FROM
      tag_mst T2
    WHERE
      T2.store_id = T1.store_id AND T2.display_flg = 1
    GROUP BY
      T2.store_id
  ) +(
  SELECT COUNT(*)
  FROM
    tag_mst T3
  WHERE
    T3.groupstore_id =(
    SELECT
      T4.groupstore_id
    FROM
      store_mst T4
    WHERE
      T4.store_id = T1.store_id
  ) AND T3.display_flg = 1
) AS cnt
FROM
  tag_mst T1
WHERE
  store_id = /*storeId*/101
GROUP BY
  T1.store_id
) T5
WHERE
  cnt > 5

もう少し良い書き方がしたいのですが、アドバイス頂けないでしょうか?
よろしくお願い致します。


補足

CREATE TABLE `tag_mst` (
  `tag_id` int(11) NOT NULL,
  `store_id` int(11) DEFAULT NULL,
  `groupstore_id` int(11) DEFAULT NULL,
  `display_flg` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `store_mst` (
  `store_id` int(11) NOT NULL,
  `groupstore_id` int(11) DEFAULT NULL,
  `store_name` text
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('1', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('2', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('3', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('4', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('5', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('6', '101', NULL, '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('11', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('12', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('13', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('14', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('15', NULL, '201', '1');
INSERT INTO `tag_mst` (`tag_id`, `store_id`, `groupstore_id`, `display_flg`) VALUES ('16', NULL, '201', '1');

INSERT INTO `store_mst` (`store_id`, `groupstore_id`, `store_name`) VALUES ('101', '201', 'name');

ALTER TABLE `store_mst` ADD UNIQUE(`store_id`);

補足2

伝わりづらいようなので要件の説明を追加します。

チェーン店で設定した商品分類タグはそれに紐づく店舗でも共通して画面表示されるので、チェーン店の共通タグと店舗個別のタグを足した合計が5件を超えるstore_idを取得したい

というものになります。回答者様のコメント欄にも詳しく記載してますので併せてご覧下さい。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

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

  • k499778

    2018/06/28 09:45

    今要件がうまく伝わっておらず、想定した結果が返ってきていないのでそれでBAつけあぐねています。saziさんのアドバイスの方向性でかなり満足しています。あとは想定した結果さえ取れれば。saziさんのコメントに悩みんでいるところの部分は記載しています。

    キャンセル

  • mts10806

    2018/06/28 09:47

    コメント部分だけだと共有されないので要件が変わってきている部分、修正事項については質問本文に反映しておくようにしてください(既にされているかもしれませんが、念のため) もっといえば、タイトルは変えたほうがいいかも。

    キャンセル

  • arasi

    2018/06/28 10:12

    質問とはかんけいないが InnoDB DEFAULT CHARSET=latin1 にしていて文字化けしないのか?

    キャンセル

回答 2

checkベストアンサー

+5

質問のSQLを分析します

SELECT T5.store_id
FROM (
  SELECT  T1.store_id
        , (
            SELECT COUNT(*) FROM tag_mst T2 
            WHERE T2.display_flg = 1 AND T2.store_id=T1.store_id  GROUP BY T2.store_id
          ) + (
            SELECT COUNT(*) FROM tag_mst T3 
            WHERE T3.display_flg = 1 AND T3.groupstore_id=(
              SELECT T4.groupstore_id FROM store_mst T4 WHERE T4.store_id = T1.store_id
            )
          ) 
        AS cnt
  FROM tag_mst T1
  WHERE store_id = /*storeId*/101
  GROUP BY T1.store_id
) T5
WHERE cnt > 5


指定した店舗の件数が>5の結果しか返却しないので、これでは要件満たせていませんね。

店舗マスタと商品分類マスタを結合(or条件がポイント)し、店舗別の共通分類を含めた一覧としてそれを集計する

select  str.store_id
from  store_mst str inner join tag_mst tag 
      on  str.groupstore_id=tag.groupstore_id or str.store_id=tag.store_id
where tag.display_flg = 1
  and str.groupstore_id in (
        select groupstore_id from store_mst where store_id=/*storeId*/101
      )
group by str.store_id
having count(*)>5


質問のSQLが要件を満たしている場合、指定した店舗のみの状況は件数のみで判断できるので、以下のようにすると簡潔になります。

select  count(*)
from  store_mst str inner join tag_mst tag 
      on  str.groupstore_id=tag.groupstore_id or str.store_id=tag.store_id
where tag.display_flg = 1
  and str.store_id=/*storeId*/101

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/06/28 01:09 編集

    回答ありがとうございます。またこちらでもいろいろ試してから返答致します。

    キャンセル

  • 2018/06/28 01:24

    一行目のstore_idをtag_mstから取得しているのでnullで返ってきてしまう場合があります。(groupstore_idで条件があった場合)
    それに対してうまく対処する方法はないでしょうか?

    キャンセル

  • 2018/06/28 01:32 編集

    データ確認していなかったのでちょっと待って下さい。

    キャンセル

  • 2018/06/28 01:39

    はい。ありがとうございます。
    あと誤解を生んでしまったかもしれませんが、orのイメージでした。
    (前)ログインユーザーが所属する店舗(store_mst)"と"そのチェーン店(groupstore_mst)に紐づく
    (今)ログインユーザーが所属する店舗(store_mst)"あるいは"、そのチェーン店(groupstore_mst)に紐づく
    少しわかりやすく書き換えました。ご配慮いただけると助かります。

    キャンセル

  • 2018/06/28 01:42

    パターン1に関しては現行のままだとエラーになり、以下のようにすると動きました。
    ただ想定される101が返されず,nullが返されました。
    ANYをつけました。
    select store_id from tag_mst
    where display_flg = 1
    and groupstore_id = ANY(select groupstore_id from store_mst
    where store_id=101)
    group by store_id
    having count(*)>5

    キャンセル

  • 2018/06/28 01:54

    要件として店舗IDを求めるとありますが、店舗IDは指定の条件なので、ややこしくなるだけなので、件数を返却するものとしました。その件数で判定すれば良いでしょうし。

    キャンセル

  • 2018/06/28 01:55

    phpMyAdminで実行しているのですがエラーになってしまいます。(#1242 - Subquery returns more than 1 row)
    なのでANYをサブクエリの前に入れて実行しました。
    想定通りの結果が返ってきています。
    他のデータも入れて試してみます。

    select store_id from tag_mst
    where display_flg = 1
    and store_id in (
    select store_id from store_mst
    where groupstore_id = ANY(
    select groupstore_id from store_mst
    where store_id=/*storeId*/5100
    )
    )
    group by store_id
    having count(*)>5

    キャンセル

  • 2018/06/28 02:06

    確認しているデータが違うからですね。
    select groupstore_id from store_mst where store_id=/*storeId*/5100
    これが複数件返却されるのですか?
    店舗マスタで店舗IDでユニークじゃないって・・・

    キャンセル

  • 2018/06/28 02:10

    しかも、SQL変わってて、ORじゃなくなってるし・・・

    キャンセル

  • 2018/06/28 02:27

    ログインユーザーが所属する店舗が含まれるチェーン店のうち、商品件数が>5の店舗ということ?

    キャンセル

  • 2018/06/28 02:36

    コメントを見るとそのようなので、修正しました。

    キャンセル

  • 2018/06/28 07:40

    ALTER TABLE `store_mst` ADD UNIQUE(`store_id`);にしてデータを整理すると,ANYをつけなくてもエラーが起きなくなりました。質問の投稿にも追加しておきます。

    ありがとうございます。

    キャンセル

  • 2018/06/28 09:42

    SQL文の方試してみたのですが、
    想定の結果がやはり返ってきませんでした。
    ログインユーザーの店舗IDに紐づくチェーン店IDのタグ(where tag_mst.groupstore_id= 201)が5件超える場合もその店舗IDを取得したいです。

    例えば
    where tag_mst.groupstore_id= 201 のものが6件、where tag_mst.store_id= 101 のものが0件 だとしても合計5件を超えるのでstore_id101を取得します。

    今だと絞りに絞っているので、そういった場合取れてきません。
    そこも考慮に入れて頂けると助かります。

    キャンセル

  • 2018/06/28 10:11

    tag_mstのstore_idが設定されているもの
    ・店舗ごとの商品分類
    tag_mstのgroupstore_idが設定されているもの
    ・チェーン店の共通商品分類
    に対して、指定した店舗の属するチェーン店で上記分類が>5の条件の店舗
    (チェーン店共通商品分類>5なら結局そのチェーン店店舗全て)
    という仕様ですか?
    上記仕様を確認する為にも、質問のSQLをエラーが無い状態にしてと言っているんですが

    キャンセル

  • 2018/06/28 10:23

    失礼しました。SQLを修正致しましたのでご確認頂ければと思います。

    キャンセル

  • 2018/06/28 10:27

    そうですね。仕様としてもそれで合っています。なのでチェーン店が5件超えていればそれに紐づく店舗IDは全て取得ですね。

    チェーン店で設定した商品分類タグはそれに紐づく店舗でも共通して画面表示されるので、チェーン店の共通タグと店舗個別のタグを足した合計が5件を超えるstore_idを取得したいです。お手数ですがお願いできたらと思います。

    キャンセル

  • 2018/06/28 11:03

    全面的に回答修正

    キャンセル

  • 2018/06/28 12:59

    ありがとうございます。すごく助かりました。最後までお付き合い頂きありがとうございました。感謝しています

    キャンセル

  • 2018/06/28 13:05

    性能はどのくらい改善されました?

    キャンセル

  • 2018/06/28 13:57

    性能としてあまり大きな変化は見られましんでしたが、コードが圧倒的にスッキリしました。大量データのテストはまだなのでその時また性能を感じたいと思います。

    キャンセル

+1

SQLの実行にはSQLの中で使っているテーブルの定義が必要ですから、各テーブルのCREATE TABLE文を、動作確認できるだけの(今回は [件数が合わせて5件を超える店舗ID]) のデータのINSERT文も質問にあった方がコメントが付き易いです。

以下、質問を訂正されては?

2カ所全角の空白が入っています。

  □T5.store_id
 store_id = /*storeId*/□5100 

同じテーブルを何度もSELECTするのなるべく避けた方がパフォーマンスが良いです。

今回の抽出条件に

 store_id = 5100 

とありますが、これは最初(インランビューの中で)に対象データをしぼった方がパフォーマンスが良いです。

メンテの為にもテーブルの別名であろうとどのテーブルであるかわかり易く

     tag_mst T2

     tag_mst TM

などとされては?

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/06/28 01:10

    回答ありがとうございます。またこちらでもいろいろ試してから返答差し上げたいと思います。

    キャンセル

  • 2018/06/28 04:34

    全角の空白がもう1つ残っています。
    > □T5.store_id
    メモ帳でコードを書いているならサクラエディタを使っては?
    http://monaski.hatenablog.com/entry/2015/06/06/232751

    > `display_flg` int(11) DEFAULT '0'
    数値はシングルクォートで囲まないでください。
    INSERT文も直してください。

    キャンセル

  • 2018/06/28 08:24

    修正しました。ご指摘ありがとうございます。

    キャンセル

  • 2018/06/28 10:02

    ちゃんと読んでください。
    INSERT文が直っていません。

    キャンセル

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

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

関連した質問

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

  • MySQL

    5844questions

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

  • SQL

    2386questions

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