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

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

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

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

Q&A

解決済

2回答

31061閲覧

SQLで2つのカラムの最大を取りたい

msx2

総合スコア174

MySQL

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

2グッド

1クリップ

投稿2017/01/20 10:07

code,version,variationという3つのカラムを持つテーブルがあります。
同じcode内で最大のversion中のさらに最大のvariationをSQLで取得する方法はないでしょうか?

code,version,variation
AAA,1,1
AAA,2,1
AAA,2,2
AAA,2,3
BBB,1,1
BBB,1,2
BBB,2,1

上記のデータだと欲しいのは
AAA,2,3
BBB,2,1
です。

単純にGroup byでMAXを取ると
AAA,2,3
BBB,2,2
となってしまいます。

この様なデータはSQLで取得するのは難しいでしょうか?
MySQLを使っています。

多少複雑になってもいいのでSQL一発で抽出する方法があれば教えてください。

KiyoshiMotoki, AketiJyuuzou👍を押しています

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

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

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

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

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

guest

回答2

0

ベストアンサー

sql

1SELECT t1.* 2FROM test AS t1 3LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version 4LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation 5WHERE t2.code IS NULL 6 AND t3.code IS NULL;

実行結果 :

sql

1mysql> CREATE TABLE test ( 2 -> code varchar(4), 3 -> version int, 4 -> variation int 5 -> ); 6Query OK, 0 rows affected (0.01 sec) 7 8mysql> INSERT INTO test VALUES 9 -> ('AAA', 1, 1), 10 -> ('AAA', 2, 1), 11 -> ('AAA', 2, 2), 12 -> ('AAA', 2, 3), 13 -> ('BBB', 1, 1), 14 -> ('BBB', 1, 2), 15 -> ('BBB', 2, 1); 16Query OK, 7 rows affected (0.00 sec) 17Records: 7 Duplicates: 0 Warnings: 0 18 19mysql> SELECT t1.* 20 -> FROM test AS t1 21 -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version 22 -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation 23 -> WHERE t2.code IS NULL 24 -> AND t3.code IS NULL; 25+------+---------+-----------+ 26| code | version | variation | 27+------+---------+-----------+ 28| AAA | 2 | 3 | 29| BBB | 2 | 1 | 30+------+---------+-----------+ 312 rows in set (0.00 sec)

追記

一つずつ実行してようやく理解できました。
自分より大きいversionがないデータがNULLになって最大を求めているんですね。
そしてもうひとつs3テーブルも同じようにjoinしてNULLが最大になる。

Good Job!
まさに、仰る通りです。

以下のように SELECTリストを*に変更し、さらに WHERE句を削除したクエリを実行してみると、
よりイメージが掴みやすくなるかと思います。

sql

1mysql> SELECT * 2 -> FROM test AS t1 3 -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version 4 -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation; 5+------+---------+-----------+------+---------+-----------+------+---------+-----------+ 6| code | version | variation | code | version | variation | code | version | variation | 7+------+---------+-----------+------+---------+-----------+------+---------+-----------+ 8| BBB | 1 | 1 | BBB | 2 | 1 | BBB | 1 | 2 | 9| AAA | 1 | 1 | AAA | 2 | 1 | NULL | NULL | NULL | 10| AAA | 1 | 1 | AAA | 2 | 2 | NULL | NULL | NULL | 11| AAA | 1 | 1 | AAA | 2 | 3 | NULL | NULL | NULL | 12| BBB | 1 | 2 | BBB | 2 | 1 | NULL | NULL | NULL | 13| AAA | 2 | 1 | NULL | NULL | NULL | AAA | 2 | 2 | 14| AAA | 2 | 1 | NULL | NULL | NULL | AAA | 2 | 3 | 15| AAA | 2 | 2 | NULL | NULL | NULL | AAA | 2 | 3 | 16| AAA | 2 | 3 | NULL | NULL | NULL | NULL | NULL | NULL | 17| BBB | 2 | 1 | NULL | NULL | NULL | NULL | NULL | NULL | 18+------+---------+-----------+------+---------+-----------+------+---------+-----------+ 1910 rows in set (0.02 sec)

ちなみにこれはgroup byでも同じようなことができるのでしょうか?

実は、本回答は以下のページの"LEFT JOIN"バージョンの応用です。
https://dev.mysql.com/doc/refman/5.6/ja/example-maximum-column-group-row.html

もし、ご興味があれば、上のページの"相関サブクエリー"バージョンと"非相関サブクエリー"バージョンで同様の結果を得られないか、チャレンジしてみてください。
(私は力尽きたので、これ以上は深追いしません(^^; )


パフォーマンス的にはgroup byを使うよりこちらの方がよかったりしますか?

インデックスの有無やレコード数、値の分布具合にもよるので、何とも言えません。
ご自身の環境でそれぞれのバージョンのクエリを実行し、比較してみてください。

もっとも、手元の環境で確認した限りでは、
私の回答のクエリで(code, version, variation)という複合インデックスが効きました。

そのため、上記複合インデックスを作成すれば、
"相関サブクエリー"バージョン・"非相関サブクエリー"バージョンのクエリと比べて
「明らかに分が悪い」
ということにはならないかと、予想します。

sql

1mysql> ALTER TABLE test ADD INDEX (code, version, variation); 2Query OK, 0 rows affected (0.04 sec) 3Records: 0 Duplicates: 0 Warnings: 0 4 5mysql> EXPLAIN SELECT t1.* 6 -> FROM test AS t1 7 -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version 8 -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation 9 -> WHERE t2.code IS NULL 10 -> AND t3.code IS NULL; 11+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+ 12| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 13+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+ 14| 1 | SIMPLE | t1 | NULL | index | NULL | code | 21 | NULL | 7 | 100.00 | Using index | 15| 1 | SIMPLE | t2 | NULL | ref | code | code | 11 | test.t1.code | 3 | 100.00 | Using where; Using index | 16| 1 | SIMPLE | t3 | NULL | ref | code | code | 16 | test.t1.code,test.t1.version | 1 | 100.00 | Using where; Using index | 17+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+ 183 rows in set, 1 warning (0.05 sec)

投稿2017/01/20 10:35

編集2017/01/20 12:13
KiyoshiMotoki

総合スコア4791

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

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

msx2

2017/01/20 10:52

ご回答ありがとうございます。 凄い!!イマイチ理解できないのですが確かに期待通りのデータが得られました! 自力では絶対にこのSQLにはたどり着かなかったと思います。 もしよければ補足いただけないでしょうか。。?厚かましい話ですが…。
msx2

2017/01/20 11:16

一つずつ実行してようやく理解できました。 自分より大きいversionがないデータがNULLになって最大を求めているんですね。 そしてもうひとつs3テーブルも同じようにjoinしてNULLが最大になる。 それぞれの最大がNULLのデータになるので、今回は両方NULLのデータを使いましたが、s3だけNULLのデータはそのversion内での最大variationになるんですね!! ちなみにこれはgroup byでも同じようなことができるのでしょうか? パフォーマンス的にはgroup byを使うよりこちらの方がよかったりしますか?
KiyoshiMotoki

2017/01/20 12:14

msx2様 レスありがとうございます。 回答欄に返答を追記させていただきましたので、ご確認ください。
msx2

2017/01/20 12:27

追記ありがとうございます。 SQLは嫌いではないのでこれからも勉強してレベルアップを目指したいと思います。 下手すると1日悩むところが10分で解決してしまったという、これがまさにteratailなんですね
guest

0

手元でSQLを書ける環境がないので概念のみになりますが、

  1. codeごとにGROPU BYして、MAX(version)とcodeの2つを並べてSELECT
  2. codeとversionでGROPU BYして、code, version, MAX(variation)の3つをSELECT

上の2つのサブクエリをINNER JOINすれば、目的のものは取れると思います。

投稿2017/01/20 10:29

maisumakun

総合スコア145364

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

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

msx2

2017/01/20 10:54

ご回答ありがとうございました!!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.44%

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

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

質問する

関連した質問