SQLSERVER パズル63　MINの扱いが難しく理解できない、相関サブクエリピクチャ

パズル63　問題文
dataのまとまりで連続しているnumを表示したい。

MINというと結果が1つのはずだが、ある条件になると複数を出力するみたい。
その条件がよくわかっていない。＞＞わかった。相関サブクエリだと繰り返し実行されるために複数回実行される
MINはたいていGROUPを使っていくつかのまとまり単位で最小値を出力する。つまりまとまりの中で1つだ。
この条件に当てはまらないのは、結合した条件で、結合されていないテーブルとの条件が加わったときに
MINが複数出力されるのかもしれない。がよくわかっていないので、またいつか勉強することにする。

調べると相関サブクエリとういことらしい
FROM句に関係のないテーブルが存在する。
Tは存在するが、T1テーブルは、サブクエリの外側にある。
メインクエリのテーブルの行ごとに、サブクエリが繰り返し実行されるらしい。
だからこの場合MINがあってもグループ単位の一回ごとの出力で出力されることになる。だからMINが条件に一致すればその都度出力される。（文章で説明するのは難しいものだ）
親のレコード１に対してサブクエリが１回実行されるため、処理が遅くなるかもしれない。

なんとなく理屈がわかってきた。この場合、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のまとまりは１，２、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--これなら出力されるそしてMIｎだと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--これなら出力されるそしてMIｎだと1件しかでないが
			--GROUPにわけることで複数出力される
			 GROUP BY T.data, T.num)

--
--MINの動作確認。MINはまとまりで最小値をだす。しかしここでMINが１件ではない条件が存在するようだ。
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は結合された１つのテーブルなのでMINは１つ
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;
 --答え１

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;

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のまとまりは１，２、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--これなら出力されるそしてMIｎだと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--これなら出力されるそしてMIｎだと1件しかでないが

--GROUPにわけることで複数出力される

GROUP BY T.data, T.num)

--MINの動作確認。MINはまとまりで最小値をだす。しかしここでMINが１件ではない条件が存在するようだ。

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は結合された１つのテーブルなのでMINは１つ

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;

--答え１

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;

こちらの記事もどうぞ

P131 SQLパズルすべての製品を購入した人クエリの番号が画像のSELECT出力と同じはず。 /* P131 […]
SQLSERVER勉強中、SQL実践入門P255成績のところ　行を列に /* SQL実践入門P255成績のところを勉強中 */ 生徒に対して、複数の教科があり、生徒 […]
SQLSERVER　クエリ、ALL演算子、puzzel11_作業依頼 /* puzzel11_作業依頼 C:完了 W:待機 step_nbrが0でstep_st […]
SQLSERVER　テーブルから新規テーブルを作成するテーブルのデータに間違いがあって、ストアドやクエリから出力されたデータから間違いを探したいときに、 […]
SQLSERVER　SELECT TOP にはできるだけORDER BYをつける先日、サーバによってSELECT TOP 1 AA FROM […]
selectして別のテーブルにINSERTするCREATEなしで、EXCEPT差分まとめ（１）新規テーブルに別のテーブルのデータをコピーしたい select * into […]