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一発で抽出する方法があれば教えてください。

行動規範の内容に同意します

回答2件

ベストアンサー

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を使うよりこちらの方がよかったりしますか？