SQLパズル　p83 セールスマンの売上ランキング　解法１学習メモ

Posted at 2024-12-14

概要

SQLパズル　p83 セールスマンの売上ランキング　解法１の学習メモです。

自己結合（非等値結合）で組み合わせを求めて、Having句で部分集合を割り出しています。

要件

地区ごとの上位３位のセールスマンの情報を取得したい。

SQL

DDL

drop Table if EXISTS SalesData;
CREATE TABLE SalesData
(district_nbr INTEGER NOT NULL,
 sales_person CHAR(10) NOT NULL,
 sales_id INTEGER NOT NULL,
 sales_amt DECIMAL(5,2) NOT NULL);

INSERT INTO SalesData VALUES(1, 'カーリー'     ,   5,  3.00  );
INSERT INTO SalesData VALUES(1, 'ハーポ'       ,  11,  4.00  );
INSERT INTO SalesData VALUES(1, 'ラリー'       ,   1, 50.00  );
INSERT INTO SalesData VALUES(1, 'ラリー'       ,   2, 50.00  );
INSERT INTO SalesData VALUES(1, 'ラリー'       ,   3, 50.00  );
INSERT INTO SalesData VALUES(1, 'モー'         ,   4,  5.00  );
INSERT INTO SalesData VALUES(2, 'ディック'     ,   8,  5.00  );
INSERT INTO SalesData VALUES(2, 'フレッド'     ,   7,  5.00  );
INSERT INTO SalesData VALUES(2, 'ハリー'       ,   6,  5.00  );
INSERT INTO SalesData VALUES(2, 'トム'         ,   7,  5.00  );
INSERT INTO SalesData VALUES(3, 'アーヴィン' ,  10,  5.00  );
INSERT INTO SalesData VALUES(3, 'メルヴィン'   ,   9,  7.00  );
INSERT INTO SalesData VALUES(4, 'ジェニー'     ,  15, 20.00  );
INSERT INTO SalesData VALUES(4, 'ジェシー'     ,  16, 10.00  );
INSERT INTO SalesData VALUES(4, 'メアリー'     ,  12, 50.00  );
INSERT INTO SalesData VALUES(4, 'オプラ'       ,  14, 30.00  );
INSERT INTO SalesData VALUES(4, 'サリー'       ,  13, 40.00  );

答えのSQL

-- 19 セールスマンの売上ランキング その１
SELECT S0.district_nbr,S0.sales_person
FROM SalesData as S1, SalesData AS S0
WHERE S0.district_nbr = S1.district_nbr
AND S0.sales_amt <= S1.sales_amt
GROUP BY S0.district_nbr,S0.sales_person
HAVING COUNT(DISTINCT S1.sales_person) <= 3
ORDER BY S0.district_nbr,S0.sales_person	
;


-- 自己結合で地区番号毎の重複順列を作る。
-- 6*6(地区番号１) + 4*4(地区番号2) + 2*2(地区番号3) + 5*5(地区番号4) = 81
-- レコード数は81になる。
SELECT *
FROM SalesData as S1, SalesData AS S0
WHERE S0.district_nbr = S1.district_nbr
;


-- 部分集合を作る前のレコード
-- S0.sales_person以上に売上を出しているS1.sale_personを表示する。
SELECT S0.district_nbr,S0.sales_person,S1.sales_person,S0.sales_amt,S1.sales_amt
FROM SalesData as S1, SalesData AS S0
WHERE S0.district_nbr = S1.district_nbr
AND (S0.sales_amt < S1.sales_amt 
	 OR S0.sales_amt = S1.sales_amt
	)
	ORDER BY S0.district_nbr,S0.sales_person
;
  
-- 部分集合を作る前のレコード
-- COUNT(DISTINCT S1.sales_person)　＝自分を含む上位のセールスマンの人数 
--  1の時：一位　２：２位　３：３位
SELECT S0.district_nbr,S0.sales_person,COUNT(DISTINCT S1.sales_person) as Ranking
FROM SalesData as S1, SalesData AS S0
WHERE S0.district_nbr = S1.district_nbr
AND (S0.sales_amt < S1.sales_amt 
	 OR S0.sales_amt = S1.sales_amt
	)
GROUP BY S0.district_nbr,S0.sales_person	
;