SQLパズル 58欠番探し その１ の学習メモ

概要

SQLパズル　p238 の 解法１の学習メモです。
Having句を使った解法です。

要件

buyer_nameにおいて、連番になっていないレコードを取得したい。

SQL

DDL

-- p238  58 欠番探し　バージョン２
drop table if exists tickets;

CREATE TABLE Tickets
(buyer_name CHAR(5) NOT NULL,
 ticket_nbr INTEGER DEFAULT 1 NOT NULL
    CHECK (ticket_nbr > 0),
    PRIMARY KEY (buyer_name, ticket_nbr));

INSERT INTO Tickets VALUES ('a', 2);
INSERT INTO Tickets VALUES ('a', 3);
INSERT INTO Tickets VALUES ('a', 4);
INSERT INTO Tickets VALUES ('b', 4);
INSERT INTO Tickets VALUES ('c', 1);
INSERT INTO Tickets VALUES ('c', 2);
INSERT INTO Tickets VALUES ('c', 3);
INSERT INTO Tickets VALUES ('c', 4);
INSERT INTO Tickets VALUES ('c', 5);
INSERT INTO Tickets VALUES ('d', 1);
INSERT INTO Tickets VALUES ('d', 6);
INSERT INTO Tickets VALUES ('d', 7);
INSERT INTO Tickets VALUES ('d', 9);
INSERT INTO Tickets VALUES ('e', 10);


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

-- 58欠番探し　バージョン２　その１ 
--  Having句を使った場合
SELECT buyer_name
FROM Tickets
GROUP BY buyer_name
Having NOT (MAX(ticket_nbr) - MIN(ticket_nbr) <= count(*))
-- 下でも成り立つ 
-- Having  (MAX(ticket_nbr) - MIN(ticket_nbr)  > count(*))	
;


-- Having句を外して、SELECT句以下にmax,min,countを加える
-- 連番の場合　max(ticket_nbr)-min(ticket_nbr) -1 =count(*)だと確認できる
SELECT buyer_name,min(ticket_nbr),max(ticket_nbr),max(ticket_nbr)-min(ticket_nbr) as diff_max_min,count(*)
FROM Tickets
GROUP BY buyer_name
;

参考

SQLパズル　p238

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

達人に学ぶSQL徹底指南書第２版 p131 Having句の力　歯抜けの無い連番

github差分
https://github.com/RYA234/SQL_Puzzle_Learning/commit/e0cd0f3a3e941dd3f0e78c127cf06a91cb7f7766