SQLパズル　p235 57 欠番探し　バージョン１ 解法１の学習メモ

# 概要
SQLパズル　p235 57 欠番探し　バージョン１ 解法１の学習メモです。
検索CASE式の条件部分を使って、連番かどうかを判定しています。


# 要件
テーブルのカラムが連番であるかどうか判定する。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/616e4e31-781c-6e9a-8b27-3cd0f49bb382.png)


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/e2a6e837-3d96-2d21-fbe9-4fad5c9b89b7.png)



# SQL

## DDL
```sql
-- p235 57 欠番探し　バージョン１ 
DROP TABLE IF EXISTS Numbers;
CREATE TABLE Numbers (seq INTEGER NOT NULL PRIMARY KEY);

INSERT INTO Numbers VALUES (2);
INSERT INTO Numbers VALUES (3);
INSERT INTO Numbers VALUES (4);
INSERT INTO Numbers VALUES (5);
INSERT INTO Numbers VALUES (6);
INSERT INTO Numbers VALUES (7);
INSERT INTO Numbers VALUES (8);

DROP TABLE IF EXISTS Numbers_no_seq;
CREATE TABLE Numbers_no_seq (seq INTEGER NOT NULL PRIMARY KEY);

INSERT INTO Numbers_no_seq VALUES (2);
INSERT INTO Numbers_no_seq VALUES (3);
INSERT INTO Numbers_no_seq VALUES (5);
INSERT INTO Numbers_no_seq VALUES (7);
INSERT INTO Numbers_no_seq VALUES (8);
INSERT INTO Numbers_no_seq VALUES (14);
INSERT INTO Numbers_no_seq VALUES (20);


drop table if exists sequence_1;
CREATE Table  sequence_1 (seq INTEGER NOT NULL PRIMARY KEY);
INSERT INTO sequence_1 VALUES (1);
INSERT INTO sequence_1 VALUES (2);
INSERT INTO sequence_1 VALUES (3);
INSERT INTO sequence_1 VALUES (4);
INSERT INTO sequence_1 VALUES (5);
INSERT INTO sequence_1 VALUES (6);
INSERT INTO sequence_1 VALUES (7);
INSERT INTO sequence_1 VALUES (8);
INSERT INTO sequence_1 VALUES (9);
INSERT INTO sequence_1 VALUES (10);
INSERT INTO sequence_1 VALUES (11);
INSERT INTO sequence_1 VALUES (12);
INSERT INTO sequence_1 VALUES (13);
INSERT INTO sequence_1 VALUES (14);
INSERT INTO sequence_1 VALUES (15);
INSERT INTO sequence_1 VALUES (16);
INSERT INTO sequence_1 VALUES (17);
INSERT INTO sequence_1 VALUES (18);
INSERT INTO sequence_1 VALUES (19);
INSERT INTO sequence_1 VALUES (20);
INSERT INTO sequence_1 VALUES (21);
INSERT INTO sequence_1 VALUES (22);
INSERT INTO sequence_1 VALUES (23);
INSERT INTO sequence_1 VALUES (24);
INSERT INTO sequence_1 VALUES (25);
INSERT INTO sequence_1 VALUES (26);
INSERT INTO sequence_1 VALUES (27);
INSERT INTO sequence_1 VALUES (28);
INSERT INTO sequence_1 VALUES (29);
INSERT INTO sequence_1 VALUES (30);
INSERT INTO sequence_1 VALUES (31);
INSERT INTO sequence_1 VALUES (32);
INSERT INTO sequence_1 VALUES (33);
INSERT INTO sequence_1 VALUES (34);
INSERT INTO sequence_1 VALUES (35);
INSERT INTO sequence_1 VALUES (36);
INSERT INTO sequence_1 VALUES (37);
INSERT INTO sequence_1 VALUES (38);
INSERT INTO sequence_1 VALUES (39);
INSERT INTO sequence_1 VALUES (40);
INSERT INTO sequence_1 VALUES (41);
INSERT INTO sequence_1 VALUES (42);
INSERT INTO sequence_1 VALUES (43);
INSERT INTO sequence_1 VALUES (44);
INSERT INTO sequence_1 VALUES (45);
INSERT INTO sequence_1 VALUES (46);
INSERT INTO sequence_1 VALUES (47);
INSERT INTO sequence_1 VALUES (48);
INSERT INTO sequence_1 VALUES (49);
INSERT INTO sequence_1 VALUES (50);
INSERT INTO sequence_1 VALUES (51);
INSERT INTO sequence_1 VALUES (52);
INSERT INTO sequence_1 VALUES (53);
INSERT INTO sequence_1 VALUES (54);
INSERT INTO sequence_1 VALUES (55);
INSERT INTO sequence_1 VALUES (56);
INSERT INTO sequence_1 VALUES (57);
INSERT INTO sequence_1 VALUES (58);
INSERT INTO sequence_1 VALUES (59);
INSERT INTO sequence_1 VALUES (60);
INSERT INTO sequence_1 VALUES (61);
INSERT INTO sequence_1 VALUES (62);
INSERT INTO sequence_1 VALUES (63);
INSERT INTO sequence_1 VALUES (64);
INSERT INTO sequence_1 VALUES (65);
INSERT INTO sequence_1 VALUES (66);
INSERT INTO sequence_1 VALUES (67);
INSERT INTO sequence_1 VALUES (68);
INSERT INTO sequence_1 VALUES (69);
INSERT INTO sequence_1 VALUES (70);
INSERT INTO sequence_1 VALUES (71);
INSERT INTO sequence_1 VALUES (72);
INSERT INTO sequence_1 VALUES (73);
INSERT INTO sequence_1 VALUES (74);
INSERT INTO sequence_1 VALUES (75);
INSERT INTO sequence_1 VALUES (76);
INSERT INTO sequence_1 VALUES (77);
INSERT INTO sequence_1 VALUES (78);
INSERT INTO sequence_1 VALUES (79);
INSERT INTO sequence_1 VALUES (80);
INSERT INTO sequence_1 VALUES (81);
INSERT INTO sequence_1 VALUES (82);
INSERT INTO sequence_1 VALUES (83);
INSERT INTO sequence_1 VALUES (84);
INSERT INTO sequence_1 VALUES (85);
INSERT INTO sequence_1 VALUES (86);
INSERT INTO sequence_1 VALUES (87);
INSERT INTO sequence_1 VALUES (88);
INSERT INTO sequence_1 VALUES (89);
INSERT INTO sequence_1 VALUES (90);
INSERT INTO sequence_1 VALUES (91);
INSERT INTO sequence_1 VALUES (92);
INSERT INTO sequence_1 VALUES (93);
INSERT INTO sequence_1 VALUES (94);
INSERT INTO sequence_1 VALUES (95);
INSERT INTO sequence_1 VALUES (96);
INSERT INTO sequence_1 VALUES (97);
INSERT INTO sequence_1 VALUES (98);
INSERT INTO sequence_1 VALUES (99);
INSERT INTO sequence_1 VALUES (100);

```

## 答えのSQL

```sql
-- 57 欠番探し　バージョン１
-- 答え
SELECT CASE WHEN COUNT(*) + MIN(seq) - 1 = MAX(seq)
            THEN 'Sequence'
            ELSE 'Not Sequence' END
        FROM Numbers;



-- 連番である場合
--  COUNT(*) + MIN(seq) - 1 = MAX(seq)
-- →　7 + 2 -1 = 8
SELECT COUNT(*),Min(seq),Max(seq),CASE WHEN COUNT(*) + MIN(seq) - 1 = MAX(seq)
            THEN 'Sequence'
            ELSE 'Not Sequence' END
        FROM Numbers;

-- 連番ではない場合
--  COUNT(*) + MIN(seq) - 1 = MAX(seq)
-- →　7 + 2 -1 != 20
SELECT COUNT(*),Min(seq),Max(seq),CASE WHEN COUNT(*) + MIN(seq) - 1 = MAX(seq)
            THEN 'Sequence'
            ELSE 'Not Sequence' END
        FROM Numbers_no_seq;

```


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/a6ec3a87-84b3-8e61-1ed3-a222e9be4563.png)

# 参考

SQLパズル　p235

SQLパズル　サポートページ
https://mickindex.sakura.ne.jp/database/db_support_sqlpuzzle.html#LocalLink-p57

達人に学ぶSQL徹底指南書第２版 p3 CASE式の書式　検索CASE式