SQLパズルp268 65 製品の対象年齢の範囲 解法１

# 概要
SQLパズルp268 65  製品の対象年齢の範囲 解法１の学習メモです。

連番作成とHaving句の合せ技です。

# 要件
商品の対象年齢が1~150歳を満たしている商品を取得したい。
1つの商品に対して複数の範囲を持っており、それらを合算したうえで1~150を満たせば良い
例：
◯：1~150
◯：1~40,41~150
☓：33~150


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/a5573323-730d-2d8e-a776-e1aff6ffbd33.png)



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/c01cbdaf-17d9-898c-df28-b1ba9d893416.png)


# SQL

## DDL
```sql
DROP TABLE IF EXISTS  PriceByAge;
CREATE TABLE PriceByAge
(product_id CHAR(10) NOT NULL,
 low_age INTEGER NOT NULL,
 high_age INTEGER NOT NULL,
 CHECK (low_age < high_age),
 product_price DECIMAL (12,4) NOT NULL,
 PRIMARY KEY (product_id, low_age));

--Product1は×、Product2は○
INSERT INTO PriceByAge VALUES ('Product1', 5, 15, 20.00);
INSERT INTO PriceByAge VALUES ('Product1', 16, 60, 18.00);
INSERT INTO PriceByAge VALUES ('Product1', 65, 150, 17.00);

INSERT INTO PriceByAge VALUES ('Product2', 1, 5, 20.00);
INSERT INTO PriceByAge VALUES ('Product2', 6, 70, 25.00);
INSERT INTO PriceByAge VALUES ('Product2', 71, 150, 40.00);

--　追加　1がないため☓
INSERT INTO PriceByAge VALUES ('Product3', 2, 5, 20.00);
INSERT INTO PriceByAge VALUES ('Product3', 6, 150, 40.00);

-- 150を含むため◯
INSERT INTO PriceByAge VALUES ('Product4', 1, 5, 20.00);
INSERT INTO PriceByAge VALUES ('Product4', 6, 151, 40.00);


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);
INSERT INTO sequence_1 VALUES (101);
INSERT INTO sequence_1 VALUES (102);
INSERT INTO sequence_1 VALUES (103);
INSERT INTO sequence_1 VALUES (104);
INSERT INTO sequence_1 VALUES (105);
INSERT INTO sequence_1 VALUES (106);
INSERT INTO sequence_1 VALUES (107);
INSERT INTO sequence_1 VALUES (108);
INSERT INTO sequence_1 VALUES (109);
INSERT INTO sequence_1 VALUES (110);
INSERT INTO sequence_1 VALUES (111);
INSERT INTO sequence_1 VALUES (112);
INSERT INTO sequence_1 VALUES (113);
INSERT INTO sequence_1 VALUES (114);
INSERT INTO sequence_1 VALUES (115);
INSERT INTO sequence_1 VALUES (116);
INSERT INTO sequence_1 VALUES (117);
INSERT INTO sequence_1 VALUES (118);
INSERT INTO sequence_1 VALUES (119);
INSERT INTO sequence_1 VALUES (120);
INSERT INTO sequence_1 VALUES (121);
INSERT INTO sequence_1 VALUES (122);
INSERT INTO sequence_1 VALUES (123);
INSERT INTO sequence_1 VALUES (124);
INSERT INTO sequence_1 VALUES (125);
INSERT INTO sequence_1 VALUES (126);
INSERT INTO sequence_1 VALUES (127);
INSERT INTO sequence_1 VALUES (128);
INSERT INTO sequence_1 VALUES (129);
INSERT INTO sequence_1 VALUES (130);
INSERT INTO sequence_1 VALUES (131);
INSERT INTO sequence_1 VALUES (132);
INSERT INTO sequence_1 VALUES (133);
INSERT INTO sequence_1 VALUES (134);
INSERT INTO sequence_1 VALUES (135);
INSERT INTO sequence_1 VALUES (136);
INSERT INTO sequence_1 VALUES (137);
INSERT INTO sequence_1 VALUES (138);
INSERT INTO sequence_1 VALUES (139);
INSERT INTO sequence_1 VALUES (140);
INSERT INTO sequence_1 VALUES (141);
INSERT INTO sequence_1 VALUES (142);
INSERT INTO sequence_1 VALUES (143);
INSERT INTO sequence_1 VALUES (144);
INSERT INTO sequence_1 VALUES (145);
INSERT INTO sequence_1 VALUES (146);
INSERT INTO sequence_1 VALUES (147);
INSERT INTO sequence_1 VALUES (148);
INSERT INTO sequence_1 VALUES (149);
INSERT INTO sequence_1 VALUES (150);
INSERT INTO sequence_1 VALUES (151);
INSERT INTO sequence_1 VALUES (152);
INSERT INTO sequence_1 VALUES (153);
INSERT INTO sequence_1 VALUES (154);
INSERT INTO sequence_1 VALUES (155);
INSERT INTO sequence_1 VALUES (156);
INSERT INTO sequence_1 VALUES (157);
INSERT INTO sequence_1 VALUES (158);
INSERT INTO sequence_1 VALUES (159);
INSERT INTO sequence_1 VALUES (160);

```

## 答えのSQL

```sql

-- 65 製品の対象年齢の範囲 その１
SELECT P.product_id
    FROM PriceByAge AS P, sequence_1 AS S
    WHERE S.seq BETWEEN P.low_age AND P.high_age
    AND S.seq <= 150
    GROUP BY P.product_id
	-- 150 = Max - MIN + 1 = 150 -1 +1
    Having COUNT(seq) = 150;

-- クロスジョインで連番作成
-- sequence_1(160レコード)とPirceByAge(10レコード)の組み合わせを求める
-- 1600（=160*10）
-- PirceByAgeのレコード1件毎に、1~160の160件のレコードが作成される。
SELECT P.*,S.seq
    FROM PriceByAge AS P, sequence_1 AS S
;



-- WHERE句で条件を絞る
-- で作ったBetweenにより、レコード数がhigh_age - low_age +1件になる
SELECT P.product_id,P.low_age, P.high_age,count(*)
    FROM PriceByAge AS P, sequence_1 AS S
	WHERE S.seq BETWEEN P.low_age AND P.high_age
    AND S.seq <= 150
	GROUP BY P.product_id, P.low_age ,P.high_age
	ORDER BY P.product_id
;

-- product_idに対して部分集合を作り
-- 部分集合に対して、1-150において連番になるかどうか　判定する
-- 条件式
-- 150 - 1 + 1 = 150
SELECT P.product_id,count(*),max(S.seq),min(S.seq)
	,CASE WHEN max(S.seq) - min(S.seq) + 1 = count(*)
	THEN 'Sequence'
	ELSE 'Not Sequence' END
	,CASE WHEN max(S.seq) - min(S.seq) + 1 = 150
	THEN 'from 1 to 150'
	ELSE 'Not from 1 to 150' END
    FROM PriceByAge AS P, sequence_1 AS S
	WHERE S.seq BETWEEN P.low_age AND P.high_age
    AND S.seq <= 150
	GROUP BY P.product_id
	;

```



![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/c2e091f6-35c8-ad6a-1dc2-140e549cca1b.png)


# 参考

SQLパズル　p268

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

達人に学ぶSQL徹底指南書第２版　p131 集合の性質を調べるための条件の
達人に学ぶSQL徹底指南書第２版　p197 連番を作ろう

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