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一発で抽出する方法があれば教えてください。
気になる質問をクリップする
クリップした質問は、後からいつでもMYページで確認できます。
またクリップした質問に回答があった際、通知やメールを受け取ることができます。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
回答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総合スコア4791
0
手元でSQLを書ける環境がないので概念のみになりますが、
- codeごとにGROPU BYして、MAX(version)とcodeの2つを並べてSELECT
- codeとversionでGROPU BYして、code, version, MAX(variation)の3つをSELECT
上の2つのサブクエリをINNER JOINすれば、目的のものは取れると思います。
投稿2017/01/20 10:29
総合スコア145747
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
あなたの回答
tips
太字
斜体
打ち消し線
見出し
引用テキストの挿入
コードの挿入
リンクの挿入
リストの挿入
番号リストの挿入
表の挿入
水平線の挿入
プレビュー
質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/01/20 10:52
2017/01/20 11:16
2017/01/20 12:14
2017/01/20 12:27