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

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

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


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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/d0d42e11-03d7-2555-9461-f4b0aff97e05.png)


# SQL

## DDL
```sql
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

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

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2710623/99b57b2d-c2c6-b03e-27b9-de4d91b26667.png)


# 参考

SQLパズル　p83

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

達人に学ぶSQL徹底指南書第２版　p131 Having句の力　Count (DISTINCT col) = COUNT(col) colの値が一意である

達人に学ぶSQL徹底指南書第２版　p48 自己結合の使い方　非等値結合で組み合わせを求める。

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