回答編集履歴

1 コメントに対して追記

KiyoshiMotoki

KiyoshiMotoki score 4743

2017/01/20 21:13  投稿

```sql
SELECT t1.*
FROM test AS t1
LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version
LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation
WHERE t2.code IS NULL
 AND t3.code IS NULL;
```
実行結果 :
```sql
mysql> CREATE TABLE test (
   ->  code varchar(4),
   ->  version int,
   ->  variation int
   -> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO test VALUES
   -> ('AAA', 1, 1),
   -> ('AAA', 2, 1),
   -> ('AAA', 2, 2),
   -> ('AAA', 2, 3),
   -> ('BBB', 1, 1),
   -> ('BBB', 1, 2),
   -> ('BBB', 2, 1);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> SELECT t1.*
   -> FROM test AS t1
   -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version
   -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation
   -> WHERE t2.code IS NULL
   ->  AND t3.code IS NULL;
+------+---------+-----------+
| code | version | variation |
+------+---------+-----------+
| AAA |      2 |        3 |
| BBB |      2 |        1 |
+------+---------+-----------+
2 rows in set (0.00 sec)
```  
 
# 追記  
> 一つずつ実行してようやく理解できました。  
> 自分より大きいversionがないデータがNULLになって最大を求めているんですね。  
> そしてもうひとつs3テーブルも同じようにjoinしてNULLが最大になる。  
 
Good Job!  
まさに、仰る通りです。  
 
以下のように SELECTリストを`*`に変更し、さらに WHERE句を削除したクエリを実行してみると、  
よりイメージが掴みやすくなるかと思います。  
 
```sql  
mysql> SELECT *  
   -> FROM test AS t1  
   -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version  
   -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation;  
+------+---------+-----------+------+---------+-----------+------+---------+-----------+  
| code | version | variation | code | version | variation | code | version | variation |  
+------+---------+-----------+------+---------+-----------+------+---------+-----------+  
| BBB |      1 |        1 | BBB |      2 |        1 | BBB |      1 |        2 |  
| AAA |      1 |        1 | AAA |      2 |        1 | NULL |   NULL |     NULL |  
| AAA |      1 |        1 | AAA |      2 |        2 | NULL |   NULL |     NULL |  
| AAA |      1 |        1 | AAA |      2 |        3 | NULL |   NULL |     NULL |  
| BBB |      1 |        2 | BBB |      2 |        1 | NULL |   NULL |     NULL |  
| AAA |      2 |        1 | NULL |   NULL |     NULL | AAA |      2 |        2 |  
| AAA |      2 |        1 | NULL |   NULL |     NULL | AAA |      2 |        3 |  
| AAA |      2 |        2 | NULL |   NULL |     NULL | AAA |      2 |        3 |  
| AAA |      2 |        3 | NULL |   NULL |     NULL | NULL |   NULL |     NULL |  
| BBB |      2 |        1 | NULL |   NULL |     NULL | NULL |   NULL |     NULL |  
+------+---------+-----------+------+---------+-----------+------+---------+-----------+  
10 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](https://dev.mysql.com/doc/refman/5.6/ja/example-maximum-column-group-row.html)  
 
もし、ご興味があれば、上のページの"相関サブクエリー"バージョンと"非相関サブクエリー"バージョンで同様の結果を得られないか、チャレンジしてみてください。  
(私は力尽きたので、これ以上は深追いしません(^^; )  
 
---  
> パフォーマンス的にはgroup byを使うよりこちらの方がよかったりしますか?  
 
インデックスの有無やレコード数、値の分布具合にもよるので、何とも言えません。  
ご自身の環境でそれぞれのバージョンのクエリを実行し、比較してみてください。  
 
もっとも、手元の環境で確認した限りでは、  
私の回答のクエリで`(code, version, variation)`という複合インデックスが効きました。  
 
そのため、上記複合インデックスを作成すれば、  
"相関サブクエリー"バージョン・"非相関サブクエリー"バージョンのクエリと比べて  
「明らかに分が悪い」  
ということにはならないかと、予想します。  
 
```sql  
mysql> ALTER TABLE test ADD INDEX (code, version, variation);  
Query OK, 0 rows affected (0.04 sec)  
Records: 0 Duplicates: 0 Warnings: 0  
 
mysql> EXPLAIN SELECT t1.*  
   -> FROM test AS t1  
   -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version  
   -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation  
   -> WHERE t2.code IS NULL  
   ->  AND t3.code IS NULL;  
+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+  
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref                         | rows | filtered | Extra                   |  
+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+  
| 1 | SIMPLE     | t1   | NULL      | index | NULL         | code | 21     | NULL                        |   7 |  100.00 | Using index             |  
| 1 | SIMPLE     | t2   | NULL      | ref  | code         | code | 11     | test.t1.code                |   3 |  100.00 | Using where; Using index |  
| 1 | SIMPLE     | t3   | NULL      | ref  | code         | code | 16     | test.t1.code,test.t1.version |   1 |  100.00 | Using where; Using index |  
+----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+  
3 rows in set, 1 warning (0.05 sec)  
```

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る