teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

1

コメントに対して追記

2017/01/20 12:13

投稿

KiyoshiMotoki
KiyoshiMotoki

スコア4791

answer CHANGED
@@ -39,4 +39,81 @@
39
39
  | BBB | 2 | 1 |
40
40
  +------+---------+-----------+
41
41
  2 rows in set (0.00 sec)
42
+ ```
43
+
44
+ # 追記
45
+ > 一つずつ実行してようやく理解できました。
46
+ > 自分より大きいversionがないデータがNULLになって最大を求めているんですね。
47
+ > そしてもうひとつs3テーブルも同じようにjoinしてNULLが最大になる。
48
+
49
+ Good Job!
50
+ まさに、仰る通りです。
51
+
52
+ 以下のように SELECTリストを`*`に変更し、さらに WHERE句を削除したクエリを実行してみると、
53
+ よりイメージが掴みやすくなるかと思います。
54
+
55
+ ```sql
56
+ mysql> SELECT *
57
+ -> FROM test AS t1
58
+ -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version
59
+ -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation;
60
+ +------+---------+-----------+------+---------+-----------+------+---------+-----------+
61
+ | code | version | variation | code | version | variation | code | version | variation |
62
+ +------+---------+-----------+------+---------+-----------+------+---------+-----------+
63
+ | BBB | 1 | 1 | BBB | 2 | 1 | BBB | 1 | 2 |
64
+ | AAA | 1 | 1 | AAA | 2 | 1 | NULL | NULL | NULL |
65
+ | AAA | 1 | 1 | AAA | 2 | 2 | NULL | NULL | NULL |
66
+ | AAA | 1 | 1 | AAA | 2 | 3 | NULL | NULL | NULL |
67
+ | BBB | 1 | 2 | BBB | 2 | 1 | NULL | NULL | NULL |
68
+ | AAA | 2 | 1 | NULL | NULL | NULL | AAA | 2 | 2 |
69
+ | AAA | 2 | 1 | NULL | NULL | NULL | AAA | 2 | 3 |
70
+ | AAA | 2 | 2 | NULL | NULL | NULL | AAA | 2 | 3 |
71
+ | AAA | 2 | 3 | NULL | NULL | NULL | NULL | NULL | NULL |
72
+ | BBB | 2 | 1 | NULL | NULL | NULL | NULL | NULL | NULL |
73
+ +------+---------+-----------+------+---------+-----------+------+---------+-----------+
74
+ 10 rows in set (0.02 sec)
75
+ ```
76
+
77
+ ---
78
+ > ちなみにこれはgroup byでも同じようなことができるのでしょうか?
79
+
80
+ 実は、本回答は以下のページの"LEFT JOIN"バージョンの応用です。
81
+ [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)
82
+
83
+ もし、ご興味があれば、上のページの"相関サブクエリー"バージョンと"非相関サブクエリー"バージョンで同様の結果を得られないか、チャレンジしてみてください。
84
+ (私は力尽きたので、これ以上は深追いしません(^^; )
85
+
86
+ ---
87
+ > パフォーマンス的にはgroup byを使うよりこちらの方がよかったりしますか?
88
+
89
+ インデックスの有無やレコード数、値の分布具合にもよるので、何とも言えません。
90
+ ご自身の環境でそれぞれのバージョンのクエリを実行し、比較してみてください。
91
+
92
+ もっとも、手元の環境で確認した限りでは、
93
+ 私の回答のクエリで`(code, version, variation)`という複合インデックスが効きました。
94
+
95
+ そのため、上記複合インデックスを作成すれば、
96
+ "相関サブクエリー"バージョン・"非相関サブクエリー"バージョンのクエリと比べて
97
+ 「明らかに分が悪い」
98
+ ということにはならないかと、予想します。
99
+
100
+ ```sql
101
+ mysql> ALTER TABLE test ADD INDEX (code, version, variation);
102
+ Query OK, 0 rows affected (0.04 sec)
103
+ Records: 0 Duplicates: 0 Warnings: 0
104
+
105
+ mysql> EXPLAIN SELECT t1.*
106
+ -> FROM test AS t1
107
+ -> LEFT OUTER JOIN test AS t2 ON t1.code = t2.code AND t1.version < t2.version
108
+ -> LEFT OUTER JOIN test AS t3 ON t1.code = t3.code AND t1.version = t3.version AND t1.variation < t3.variation
109
+ -> WHERE t2.code IS NULL
110
+ -> AND t3.code IS NULL;
111
+ +----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+
112
+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
113
+ +----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+
114
+ | 1 | SIMPLE | t1 | NULL | index | NULL | code | 21 | NULL | 7 | 100.00 | Using index |
115
+ | 1 | SIMPLE | t2 | NULL | ref | code | code | 11 | test.t1.code | 3 | 100.00 | Using where; Using index |
116
+ | 1 | SIMPLE | t3 | NULL | ref | code | code | 16 | test.t1.code,test.t1.version | 1 | 100.00 | Using where; Using index |
117
+ +----+-------------+-------+------------+-------+---------------+------+---------+------------------------------+------+----------+--------------------------+
118
+ 3 rows in set, 1 warning (0.05 sec)
42
119
  ```