パズル63 問題文
dataのまとまりで連続しているnumを表示したい。
MINというと結果が1つのはずだが、ある条件になると複数を出力するみたい。
その条件がよくわかっていない。>>わかった。相関サブクエリだと繰り返し実行されるために複数回実行される
MINはたいていGROUPを使っていくつかのまとまり単位で最小値を出力する。つまりまとまりの中で1つだ。
この条件に当てはまらないのは、結合した条件で、結合されていないテーブルとの条件が加わったときに
MINが複数出力されるのかもしれない。がよくわかっていないので、またいつか勉強することにする。
調べると相関サブクエリとういことらしい
FROM句に関係のないテーブルが存在する。
Tは存在するが、T1テーブルは、サブクエリの外側にある。
メインクエリのテーブルの行ごとに、サブクエリが繰り返し実行されるらしい。
だからこの場合MINがあってもグループ単位の一回ごとの出力で出力されることになる。だからMINが条件に一致すればその都度出力される。(文章で説明するのは難しいものだ)
親のレコード1に対してサブクエリが1回実行されるため、処理が遅くなるかもしれない。
なんとなく理屈がわかってきた。この場合、INNER JOINでも代用できるらしい。
詳しくには
https://language-and-engineering.hatenablog.jp/entry/20101108/p1
■パズル63 問題文 dataのまとまりで連続しているnumを表示したい。 /* num data 1 a 2 a 3 b 6 b 8 a */ 求めたい出力 dataのaのまとまりは1,2、bのまとまりは3,6 low high data 8 8 a 1 2 a 3 6 b - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - CREATE TABLE T (num INTEGER NOT NULL PRIMARY KEY, data CHAR(1) NOT NULL); --- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - INSERT INTO T VALUES (1, 'a'), (2, 'a'), (3, 'b'), (6, 'b'), (8, 'a'); - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ■答えその1 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - SELECT * FROM T as T1 LEFT OUTER JOIN T as T2 ON T1.num = T2.num SELECT * FROM T as T1 LEFT OUTER JOIN T as T2 ON T1.num = T2.num LEFT OUTER JOIN T as T3 ON T2.num = T3.num SELECT * FROM T /* num data 1 a 2 a 3 b 6 b 8 a */ SELECT * FROM T LEFT OUTER JOIN T as T1 ON t.num = T1.num WHERE t. num > T1.num AND t.data <> T1.data--これだと0件 SELECT * FROM T LEFT OUTER JOIN T as T1 ON t. num > T1.num WHERE t.data <> T1.data --これなら出力される SELECT * FROM T LEFT OUTER JOIN T as T1 ON t. num > T1.num AND t.data <> T1.data--これなら出力される --2つのテーブル SELECT Min(T.num) FROM T LEFT OUTER JOIN T as T1 ON t. num > T1.num AND t.data <> T1.data--これなら出力されるそしてMInだと1件しかでないが --GROUPにわけることで複数出力される GROUP BY T.data, T.num; -- --3つのテーブルでも再現できる SELECT * FROM T as T1 LEFT OUTER JOIN T as T2 ON T2.num = ( SELECT MIN(num)--最小値だから1件ではなくてしかもGROUPを使わなくてもまとまりができる FROM T WHERE num > T1.num and data <> T1.data ) --正常に動かない。 /* low high 1 8 */ SELECT MIN(T1.num) AS low, MAX(T1.num) AS high --T1.data FROM T AS T1 LEFT OUTER JOIN T AS T2 ON T2.num = (SELECT Min(T.num) FROM T LEFT OUTER JOIN T as T1 ON t. num > T1.num AND t.data <> T1.data--これなら出力されるそしてMInだと1件しかでないが --GROUPにわけることで複数出力される GROUP BY T.data, T.num) -- --MINの動作確認。MINはまとまりで最小値をだす。しかしここでMINが1件ではない条件が存在するようだ。 SELECT* FROM T AS T1 LEFT OUTER JOIN T AS T2 ON T2.num = (SELECT MIN(num) FROM T )--num1 だけで1件だけかと思ったら複数行でてた --3つのテーブル SELECT * FROM T as T1 LEFT OUTER JOIN T as T2 ON T2.num = ( SELECT MIN(num) FROM T WHERE num > T1.num and data <> T1.data--この場合は、グループワケされる。という不思議 ) --ONを使わければCROSS JOINがある。しかし1行しかでない。グループ分けにならない。 --考えられるのはCROSS JOINは結合された1つのテーブルなのでMINは1つ SELECT MIN(T.num) FROM T CROSS JOIN T as T1 WHERE T.num > T1.num and T.data <> T1.data SELECT * FROM T CROSS JOIN T as T1 WHERE T.num > T1.num and T.data <> T1.data SELECT* FROM T AS T1 LEFT OUTER JOIN T AS T2 ON T2.num = (SELECT MIN(num) FROM T WHERE num > T1.num AND data <> T1.data)--num,3,8がでる -- GROUP BY T1.data, T2.num; -- SELECT * FROM T AS T1 LEFT OUTER JOIN T AS T2 ON T2.num = (SELECT MIN(num) FROM T WHERE num > T1.num AND data <> T1.data ) --GROUP BY T1.data, T2.num; SELECT * FROM T AS T1 LEFT OUTER JOIN T AS T2 ON T2.num = (SELECT MIN(num) FROM T WHERE num > T1.num AND data <> T1.data)--わからん。なんでMINなのに複数出力するんだ?data <> でまとまりができる。 --GROUP BY T1.data, T2.num; --答え1 SELECT MIN(T1.num) AS low, MAX(T1.num) AS high, T1.data FROM T AS T1 LEFT OUTER JOIN T AS T2 ON T2.num = (SELECT MIN(num) FROM T WHERE num > T1.num AND data <> T1.data) GROUP BY T1.data, T2.num;