SQLパズル pp174 41 予算 解法その１ 学習メモ

概要

SQLパズル　pp174 41 予算の解法その１のメモです。
問題の肝は　CASE式と　一行目のSELECT文の役割（重要）です。

要件

・商品番号毎のの説明(item_descr),購入額の合計(actual_tot),見積もり金額の合計（estimated_tot）、請求書番号を取得する。
・購入額の合計はActualテーブルのactual_amtの合計額
・見積もり金額の合計はEstimatesテーブルのestimated_amtの合計額
・請求書番号が２種類ある場合は mixと表示する

※tot totalの略語らしい　（いうほど略してるか？）

SQL

DDL

DROP TABLE IF EXISTS Items;
CREATE TABLE Items
(item_nbr INTEGER,
 item_descr CHAR(10));

DROP TABLE IF EXISTS Actuals;
CREATE TABLE Actuals
(item_nbr   INTEGER, 
 actual_amt DECIMAL(5,2),
 check_nbr  CHAR(4));

DROP TABLE IF EXISTS Estimates;
CREATE TABLE Estimates
(item_nbr      INTEGER, 
 estimated_amt DECIMAL(5,2));

INSERT INTO Items VALUES(10, 'Item 10');
INSERT INTO Items VALUES(20, 'Item 20');
INSERT INTO Items VALUES(30, 'Item 30');
INSERT INTO Items VALUES(40, 'Item 40');
INSERT INTO Items VALUES(50, 'item 50');

INSERT INTO Actuals VALUES(10, 300.00, '1111');
INSERT INTO Actuals VALUES(20, 325.00, '2222');
INSERT INTO Actuals VALUES(20, 100.00, '3333');
INSERT INTO Actuals VALUES(30, 525.00, '1111');

INSERT INTO Estimates VALUES(10, 300.00);
INSERT INTO Estimates VALUES(10, 50.00);
INSERT INTO Estimates VALUES(20, 325.00);
INSERT INTO Estimates VALUES(20, 110.00);
INSERT INTO Estimates VALUES(40, 25.00);

答えのSQL

-- p174 41 予算　方法　その１
SELECT * FROM
    (SELECT I1.item_nbr,I1.item_descr,
        (SELECT SUM(A1.actual_amt)
            FROM Actuals AS A1
            WHERE I1.item_nbr = A1.item_nbr) AS tot_act,
        (SELECT SUM(E1.estimated_amt)
            FROM Estimates AS E1
            WHERE I1.item_nbr = E1.item_nbr) AS estimate_tot,
        (SELECT CASE WHEN COUNT(*) = 1
                     THEN MAX(check_nbr)
                     ELSE 'Mixed' END
                FROM  Actuals AS A2
                WHERE I1.item_nbr = A2.item_nbr
                GROUP BY item_nbr) AS check_nbr
FROM Items I1) AS TMP
WHERE tot_act IS NOT NULL
    OR estimate_tot IS NOT NULL;

-- CASE部分がある呼ぶクエリのみ抜粋
-- item_nbr=40 50 は存在しない、他のサブクエリと結合すると、case部分の値はnullとなる
SELECT I1.item_nbr,MIN(check_nbr) as check_nbr_first,MAX(check_nbr) as check_nbr_second,
	CASE WHEN COUNT(*) = 1
                     THEN MAX(check_nbr)
                     ELSE 'Mixed' END
                FROM  Actuals AS A2,Items AS I1
                WHERE I1.item_nbr = A2.item_nbr
                GROUP BY I1.item_nbr
;
-- 最初のFROM句以下の内容
SELECT I1.item_nbr,I1.item_descr,
	(SELECT SUM(A1.actual_amt)
		FROM Actuals AS A1
		WHERE I1.item_nbr = A1.item_nbr) AS tot_act,
	(SELECT SUM(E1.estimated_amt)
		FROM Estimates AS E1
		WHERE I1.item_nbr = E1.item_nbr) AS estimate_tot,
	(SELECT CASE WHEN COUNT(*) = 1
				 THEN MAX(check_nbr)
				 ELSE 'Mixed' END
			FROM  Actuals AS A2
			WHERE I1.item_nbr = A2.item_nbr
			GROUP BY item_nbr) AS check_nbr
	FROM Items I1
	-- この時点では、SELECT句になっておりFROM句の内容ではないので、WHERE句を使えない
	-- WHERE句で条件指定するためFROM句のサブクエリとして使っている
	-- WHERE tot_act IS NOT NULL -- エラーになる。FROMのテーブルにtot_actがないため
	--  OR estimate_tot IS NOT NULL -- エラーになる　FROMのテーブルにtot_actがないため
		;
-- 答えのSQLからWHERE式を取り除いた部分
--  FROM句のサブクエリと同じ結果になることを確認できる。
SELECT * FROM
    (SELECT I1.item_nbr,I1.item_descr,
        (SELECT SUM(A1.actual_amt)
            FROM Actuals AS A1
            WHERE I1.item_nbr = A1.item_nbr) AS tot_act,
        (SELECT SUM(E1.estimated_amt)
            FROM Estimates AS E1
            WHERE I1.item_nbr = E1.item_nbr) AS estimate_tot,
        (SELECT CASE WHEN COUNT(*) = 1
                     THEN MAX(check_nbr)
                     ELSE 'Mixed' END
                FROM  Actuals AS A2
                WHERE I1.item_nbr = A2.item_nbr
                GROUP BY item_nbr) AS check_nbr
FROM Items I1) AS TMP;

参考

SQLパズル　p174 予算　41 解法その１

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

達人に学ぶSQL徹底指南書第２版 p18 CASE式の中で集約関数を使う

github 差分