LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@RYA234(RYA234)

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

Posted at

概要

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

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

要件

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

image.png

image.png

SQL

DDL

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


-- 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

参考

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

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?