SQLパズル　15 現在の給料と昇給前の給料　その１メモ

Last updated at 2024-11-13Posted at 2024-11-13

概要

いろんな要素が詰まってますね。
自己非等値結合とHaving句を使って部分集合の条件指定が肝ですね。

この問題解法９通りあるから重い…

要件

給料テーブルから、現在の給料と現在より直近の給料を取得したい。
（一番新しい日付のレコードと２番目に新しい日付のレコードを取得したい？？）

SQL

DDL

drop table if exists salaries cascade;
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);

答えのSQL

-- 本の答え　　viewだと作り直すの面倒だからSELECT文で書き換える
drop view if exists salaries2;  
create view	salaries2(emp_name,sal_date,sal_amt)
AS SELECt S0.emp_name,S0.sal_date,MAX(S0.sal_amt)
FROM salaries AS S0, salaries AS S1
WHERE S0.sal_date <= S1.sal_date
    AND S0.emp_name = S1.emp_name
    GROUP BY S0.emp_name, S0.sal_date
   Having Count(*) <= 2
;

-- 答え
-- 回答の方だとviewだが、検証しやすくするためにSELECT文にする
SELECT S0.emp_name,S0.sal_date,MAX(S0.sal_amt)
FROM salaries AS S0, salaries AS S1
WHERE S0.sal_date <= S1.sal_date
    AND S0.emp_name = S1.emp_name
    GROUP BY S0.emp_name, S0.sal_date
   Having Count(*) <= 2
;

-- 分解して考える

-- step1　グループ化して部分集合を確認する
名前で自己結合して, sal_dateの組み合わせを作る
SQLの結果の見やすくすために、グループ化の条件にSa
SELECT S0.emp_name, S0.sal_date as S0_sal_date,S1.sal_date as S1_sal_date,S0,count(*)
FROM salaries AS S0, salaries AS S1
WHERE  S0.emp_name = S1.emp_name
    GROUP BY S0.emp_name, S0.sal_date,S1.sal_date
;



-- step2 非等号結合を行って 
SELECT S0.emp_name, S0.sal_date as S0_sal_date,S1.sal_date as S1_sal_date,S0,count(*)
FROM salaries AS S0, salaries AS S1
WHERE S0.emp_name = S1.emp_name
	AND S0.sal_date <= S1.sal_date
    GROUP BY S0.emp_name, S0.sal_date,S1.sal_date
    ORDER BY S0.emp_name, S0.sal_date
	;


-- step3 Havingで部分集合の条件 解部分
SELECT S0.emp_name, S0.sal_date as S0_sal_date,count(*),MAX(S0.sal_amt)
FROM salaries AS S0, salaries AS S1
WHERE S0.emp_name = S1.emp_name
	AND S0.sal_date <= S1.sal_date
    GROUP BY S0.emp_name, S0.sal_date
	Having Count(*) <= 2
;

図

step1-2

step2-3
Having句でを使うことによって、現在の給料と直近の給料の情報だけを取り出すことができる
Count()=1のとき、S0.sale_date = S1.sale_dateは同じ値であり、S0.sal_amtは最新の給料
Count()=2のとき、S0.sale_date< S1.sale_dateであり、S0.sale_dateは直近の日付となる
Count(*)>3のとき、現在、直近の日付、直直近の日付となるのでこの条件は弾く