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

初めに

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

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

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

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

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

PostgreSQL で動作確認しています

テーブル と データ

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 テーブルの中身

今の給料の昇給日を取出す

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

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

▼ 出力(画像の左側)

今のお給料の出力に対して、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

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

社員IDと昇給日が同じ給料を結合する

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 で取り出している

参考文献

SQLパズル 第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック