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

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

ただいまの
回答率

90.52%

  • MySQL

    5848questions

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

  • SQL

    2389questions

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

MySQLでグループ化対象外のデータ選択

解決済

回答 3

投稿

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

ky_46

score 77

 前提・実現したいこと

すごく基本的なことかもしれませんが、SQLのデータから、特定のカラムをグループ化し、別の特定カラムの最小値のデータを持つレコードを抽出したいです。

テストテーブルとして、仕入値の最安を引っ張り出すSQLを作成してみました。

 該当のソースコード

CREATE TABLE `question` (
 `serial` INTEGER UNSIGNED auto_increment primary key
,`品名` VARCHAR(50)
,`ID` VARCHAR(50)
,`仕入提示` INTEGER UNSIGNED
,`仕入先` VARCHAR(50)
);

INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('りんご','A001',200,'青森市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('りんご','A001',210,'弘前市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('りんご','A002',320,'長野市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('りんご','A002',310,'松本市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('ぶどう','G001',410,'笛吹市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('ぶどう','G001',400,'中野市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('ぶどう','G002',370,'甲州市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('ぶどう','G002',360,'塩尻市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('さくらんぼ','C001',510,'天童市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('さくらんぼ','C001',500,'東根市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('さくらんぼ','C001',520,'山形市');
INSERT INTO `question` (`品名`,`ID`,`仕入提示`,`仕入先`) VALUES ('さくらんぼ','C002',600,'村山市');


SELECT `serial`,`品名`,`ID`,Min(`仕入提示`) ,`仕入先`
FROM `question`
GROUP BY ID;

 発生している問題・エラーメッセージ

これでは、serial仕入先が合わないデータが発生してしまいます。
serial    品名    ID    Min(仕入提示)    仕入先
1    りんご    A001    200    青森市
3    りんご    A002    310    長野市<serialと値段があわない
9    さくらんぼ    C001    500    天童市<serialと値段があわない
12    さくらんぼ    C002    600    村山市
5    ぶどう    G001    400    笛吹市<serialと値段があわない
7    ぶどう    G002    360    甲州市<serialと値段があわない

これは、この SQL では ID を GROUP 化し、仕入提示を最小にしているまでは良いとして、serialや、品名、仕入先には指定をしていないため、適当なデータを引っ張っているのではないかと思われます。

MySQLで GROUP 化と条件化で絞り込んだレコードに所属する、条件していないカラムを正確に選択する方法はありますか?

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 3

checkベストアンサー

+2

mysqlのgroup by には拡張仕様があります。
MySQLのGROUP BYは、寛容すぎて気持ちが悪い。

ID を GROUP 化し、仕入提示を最小にしているまでは良いとして、serialや、品名、仕入先には指定をしていないため、適当なデータを引っ張っているのではないかと思われます。

指定していないのだから、何を取ってきても文句は言えません。
※誤解を生みやすいので、sql_mode に ONLY_FULL_GROUP_BY を設定するのもありかと思います。

ID毎の最安値の仕入先の一覧は以下のようにして取得できます。

SELECT *
FROM question t1
where 仕入提示 = (
        select min(仕入提示) from question where id=t1.ID
      ) 


※yambejpさんの回答がついたので補足しておきます。
上記SQLは最安値が同額のものもその件数分出力されます。
この処理が自動で仕入先を決定するものであれば、発注数が重複しますから、限定するための条件が必要ですし、一旦画面に表示するなどして人的に決定する場合には、最安値が複数あることを示す情報が合ったほうが良いかと思います。

投稿

編集

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/06/11 12:50

    こちらでほぼ希望通りの動作となりました。ありがとうございます。実データでは、同一IDで、同じ価格が出たことは記憶の限りありませんので、今回は大丈夫かと思います。ありがとうございました。

    キャンセル

+1

同じIDで同じ金額の最低値を持つデータがあるときにどうしたいかという条件が足りません
往々にして2つの仕入先が提案されるのはアプリ側で解釈ができない場合があります。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/06/11 12:27

    ご回答ありがとうございます。同じIDで、同一価格があった場合は想定してませんでした。実データではまず無いと思いますので、今回は考慮しない事にします。ありがとうございます。

    キャンセル

0

私見ですが本来グループ化は「同一値をまとめる」ために使うものであって
「最小のデータを持っている行がほしい」という案件には向かないと思っています。
グループ化はグループ化として使うほうがいいと思います。

私ならSQLの解釈を変えて
「IDが同じで、仕入提示が自分より小さいデータがないもの」
とします(コードは未検証)。
yambejp氏の仰る通り仕様が足りないので最低値同一の場合、同じIDでも複数行でます。

SELECT *
FROM question as t1
where not exists(
  select 0
  from question as t2
  where t2.ID=t1.ID
    t2.仕入提示<t1.仕入提示
)

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2018/06/11 11:32 編集

    >グループ化は「同一値をまとめる」ために使うものであって
    >「最小のデータを持っている行がほしい」という案件には向かないと思っています。
    具体的な理由がありましたら、ご教示下さい。

    キャンセル

  • 2018/06/11 12:55

    こちら、私も興味あります。「グループ化して、その中で最小値を探す」のが、唯一の方法と思っていましたので…

    キャンセル

  • 2018/06/11 13:17

    私の言い方が若干過激だったかもしれませんがsazi氏の使い方はオッケーだと思っています。
    sazi氏の提示のコードはgroup byをしていません。最終のSQLでももちろんですがwhere句のサブクエリでもgroup byはしていませんよね?
    あくまで『group byをする』理由について「最小の『データ』を取得するに当たっては向かない」ということです。
    現に質問者さんはgroup byを書こうとして
    select ID,min(仕入提示)
    from question
    group by ID,?????

    こうなってしまっています。
    このsqlを生かしてserial等を正しく取得するのに向いているとは私は思っていません、ということです。

    キャンセル

  • 2018/06/11 13:23

    ky_46氏
    >「グループ化して、その中で最小値を『探す』」
    とおっしゃっている通り
    『探す』という動作はSQLにおいては本来where句で表現すべき、ということです。
    group by自体は『探す』という動作ではないのでそれのために使うべきではない、
    という感じで伝わりますでしょうか?
    sazi氏のSQLは最小値で『探して』います。

    キャンセル

  • 2018/06/11 13:27 編集

    成程、「最小の『データ』(行)を取得」するにあたっては、全体をgroup byして取得するような考え方は適切ではないということですね。

    キャンセル

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

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

関連した質問

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

  • MySQL

    5848questions

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

  • SQL

    2389questions

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