可視化SQL | 現在の給料と昇給前の給料 を１行で表示する(その３) | LEFT JOIN と MAX


# 初めに
この問題は、SQLパズル #15 『現在の給料と昇給前の給料 』 を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです

下記に表示するテーブルとデータを見ると、社員の昇給日と給料は1行づつ追加されていて
複数昇給している社員とまだ昇給していない社員がいます

この問題は、社員のお給料を保持しているテーブルから
`『現在の給料』と『昇給前の給料』を取出して、1行で表示させる問題です`

『その２』との違いは、まず最初に今のお給料をとりだして
ココに必要な情報を LEFT JOIN で結合していきます
3回 LEFT JOIN を使っているので少し複雑なSQLかな～と思います

下記画像の様な出力結果となります
各社員に対して、今のお給料の昇給日とお給料、前回の昇給日とお給料が
1行で表示されています


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/8fa312e4-c9be-33cc-27b4-23520e533098.png)



**PostgreSQL で動作確認しています**


# テーブル と データ
```SQL:SQL
CREATE TABLE Salaries (
	emp_name CHAR(10) NOT NULL,
	sal_date DATE NOT NULL,
	sal_amt DECIMAL(8,2) NOT NULL,
	PRIMARY KEY (emp_name, sal_date)
)

INSERT INTO Salaries
VALUES ('Tom',   '1996-06-20', 500.00),
       ('Tom',   '1996-08-20', 700.00),
       ('Tom',   '1996-10-20', 800.00),
       ('Tom',   '1996-12-20', 900.00),
       ('Dick',  '1996-06-20', 500.00),
       ('Harry', '1996-07-20', 500.00),
       ('Harry', '1996-09-20', 700.00);
```

▼ Salaries テーブルの中身


![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d21e370f-f58e-8717-6129-ce90d259372b.png)


# 今の給料の昇給日を取出す
```SQL:SQL
SELECT W.emp_name, MAX(W.sal_date) AS maxdate
FROM Salaries AS W
GROUP BY W.emp_name
```

|emp_name|maxdate|
|---|---|
|Tom|1996-12-20|
|Harry|1996-09-20|
|Dick|1996-06-20|

# 今の給料より~以前の昇給日を結合
```SQL:SQL
SELECT A.emp_name, A.maxdate, X.sal_date
FROM 
(
   SELECT W.emp_name, MAX(W.sal_date) AS maxdate
   FROM Salaries AS W
   GROUP BY W.emp_name
) AS A
LEFT JOIN Salaries AS X ON A.emp_name = X.emp_name AND A.maxdate > X.sal_date
```

▼ 出力(画像の左側)


![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/ee171f33-ec52-c2d5-6d45-34d8da7dcf3c.png)


今のお給料の出力に対して、Salaries テーブルを LEFT JOIN しています
結合する時の条件が下記になります
　A.emp_name = X.emp_name　`同じ社員を結合する`
　A.maxdate > X.sal_date　`今のお給料の昇給日(maxdate) より以前の昇給日を結合する`

Tomの場合
**1996-12-20 より以前の昇給日は1996-06-20, 1996-08-20, 1996-10-20 の3つ有る
その為、結合後のデータ件数は3件に増える**


# 1つ前の給料の昇給日を取出す
```SQL:SQL
SELECT A.emp_name, A.maxdate, MAX(X.sal_date) AS maxdate2
FROM
(
   SELECT W.emp_name, MAX(W.sal_date) AS maxdate
   FROM Salaries AS W
   GROUP BY W.emp_name
) AS A
LEFT JOIN Salaries AS X ON A.emp_name = X.emp_name AND A.maxdate > X.sal_date
GROUP BY A.emp_name, A.maxdate
```

▼ 出力(下記画像の右側)

|emp_name|maxdate|maxdate2|
|---|---|---|
|Tom|1996-12-20|1996-10-20|
|Harry|1996-09-20|1996-07-20|
|Dick|1996-06-20|空白|


![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/21923f3f-6274-cfcd-f41f-94dcd3dd055d.png)



# 社員IDと昇給日が同じ給料を結合する
```SQL:SQL
SELECT B.emp_name, B.maxdate, Y.sal_amt, B.maxdate2, Z.sal_amt
FROM
(
   SELECT A.emp_name, A.maxdate, MAX(X.sal_date) AS maxdate2
   FROM
   (
      SELECT W.emp_name, MAX(W.sal_date) AS maxdate
      FROM Salaries AS W
      GROUP BY W.emp_name
   ) AS A
   LEFT JOIN Salaries AS X ON A.emp_name = X.emp_name AND A.maxdate > X.sal_date
   GROUP BY A.emp_name, A.maxdate
) AS B
LEFT JOIN Salaries AS Y ON B.emp_name = Y.emp_name AND B.maxdate = Y.sal_date
LEFT JOIN Salaries AS Z ON B.emp_name = Z.emp_name AND B.maxdate2 = Z.sal_date
```

▼ 出力

|emp_name|B.maxdate|Y.sal_amt|B.maxdate2|Z.sal_amt|
|---|---|---|---|---|
|Tom|1996-12-20|900|1996-10-20|800|
|Harry|1996-09-20|700|1996-07-20|500|
|Dick|1996-06-20|500|空白|空白|

各社員の『今のお給料の昇給日』 maxdate と『1つ前の昇給日』 maxdate2 が取得できた
この昇給日と同じ昇給日の給料を Salaries テーブルから取り出す

『今の給料』を Slaries AS Y から LEFT JOIN で取り出している
『1つ前の給料』を Salaries AS Z から LEFT JOIN で取り出している


![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2290e3b7-be74-c6ea-3093-4679c4dd0292.png)



# 参考文献
[SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック](https://www.shoeisha.co.jp/book/detail/9784798114132)