パズル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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 |
■パズル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; |