NOT EXISTSの利用

投稿日：2016年12月1日

引き続きNOT EXISTSの利用です。

思った以上に使えますね・・・がムズイ。

今回は下記のようなテーブル(seats)があるとします。

ケースとしては新幹線の座席番号で空は席が空いている状態、占は誰かが座っている状態です。ここで3人が並んで座れるシートを求めるとします。

seat | status
——+——–
1 | 占
2 | 占
3 | 空
4 | 空
5 | 空
6 | 占
7 | 空
8 | 空
9 | 空
10 | 空
11 | 空
12 | 占
13 | 占
14 | 空
15 | 空

最初は以前の例のように歯抜けを求めようかと思いましたが、そもそも歯抜け自体が２つ以上重なっていることもあり没に・・・

--満たしている列を求める
 SELECT
   can_seat.seat
 FROM ( SELECT s1.seat FROM seats s1 WHERE s1.status  ='空' ) as can_seat

--歯抜けを求める
SELECT
  can_seat.seat + 1 AS add_num  
FROM ( SELECT  s1.seat FROM seats s1 WHERE   s1.status  ='空' ) as can_seat
WHERE
 can_seat.seat + 1 NOT IN ( SELECT  s1.seat FROM  seats s1 WHERE s1.status  ='空' );

--満たしている列を求める

SELECT

can_seat.seat

FROM ( SELECT s1.seat FROM seats s1 WHERE s1.status ='空' ) as can_seat

--歯抜けを求める

SELECT

can_seat.seat + 1 AS add_num

FROM ( SELECT s1.seat FROM seats s1 WHERE s1.status ='空' ) as can_seat

WHERE

can_seat.seat + 1 NOT IN ( SELECT s1.seat FROM seats s1 WHERE s1.status ='空' );

そこで始点と終点をもとめ、間に占が存在しているレコードがない、という状態を作ります。サブクエリの期間範囲指定の問題で扱った考え方です。

イメージとしてはまずいつものように自己結合でテーブルを求めます。

SELECT
  s1.seat as start_seat ,
  '～' as connect_string ,
  s2.seat as end_seat
FROM
  seats s1 , seats s2

SELECT

s1.seat as start_seat ,

'～' as connect_string ,

s2.seat as end_seat

FROM

seats s1 , seats s2

ここで下記のようなテーブルが出力されます
start_seat | connect_string | end_seat
————+—————-+———-
1 | ～ | 1
‥‥‥‥‥(略)‥‥‥‥‥‥‥‥‥‥‥‥‥‥‥‥‥
15 | ～ | 15

ここでs1とs2の間に占が存在しないという条件を作ります。

SELECT
  s1.seat as start_seat ,
  '～' as connect_string ,
  s2.seat as end_seat
FROM
  seats s1 , seats s2
WHERE
  --前後は確認しておきましょう
  s2.seat >  s1.seat 
  AND
   --ここで開始点と終点中に存在しないを表現する
  NOT EXISTS (
   SELECT
     s3.*
   FROM
     seats s3
   WHERE
     s3.status = '占' AND
     s3.seat BETWEEN s1.seat AND s2.seat
);

SELECT

s1.seat as start_seat ,

'～' as connect_string ,

s2.seat as end_seat

FROM

seats s1 , seats s2

WHERE

--前後は確認しておきましょう

s2.seat > s1.seat

AND

--ここで開始点と終点中に存在しないを表現する

NOT EXISTS (

SELECT

s3.*

FROM

seats s3

WHERE

s3.status = '占' AND

s3.seat BETWEEN s1.seat AND s2.seat

);

start_seat | connect_string | end_seat
————+—————-+———-
3 | ～ | 4
3 | ～ | 5
4 | ～ | 5
7 | ～ | 8
7 | ～ | 9
7 | ～ | 10
7 | ～ | 11
8 | ～ | 9
8 | ～ | 10
8 | ～ | 11
9 | ～ | 10
9 | ～ | 11
10 | ～ | 11
14 | ～ | 15

上記だと条件に合致したレコードをすべて出力するので
例えば3人だったらs1+2 =s2としておきましょう。

SELECT
  s1.seat as start_seat ,
  '～' as connect_string ,
  s2.seat as end_seat
FROM
  seats s1 , seats s2
WHERE
   --ここで開始点と終点中に存在しないを表現する
   --必然的にs2.seat > s1.seatになるので以前使った条件がいらない
  s2.seat = s1.seat + 2 AND
  NOT EXISTS (
   SELECT
     s3.*
   FROM
     seats s3
   WHERE
     s3.status = '占' AND
     s3.seat BETWEEN s1.seat AND s2.seat
);

SELECT

s1.seat as start_seat ,

'～' as connect_string ,

s2.seat as end_seat

FROM

seats s1 , seats s2

WHERE

--ここで開始点と終点中に存在しないを表現する

--必然的にs2.seat > s1.seatになるので以前使った条件がいらない

s2.seat = s1.seat + 2 AND

NOT EXISTS (

SELECT

s3.*

FROM

seats s3

WHERE

s3.status = '占' AND

s3.seat BETWEEN s1.seat AND s2.seat

);

下記が回答になります。

start_seat | connect_string | end_seat
————+—————-+———-
3 | ～ | 5
7 | ～ | 9
8 | ～ | 10
9 | ～ | 11