SQL練習 ‐ 給与が 2 番目に高い従業員の情報を取得しましょう

概要

今回のテーマは、集計関数max()、サブクエリ、window関数などを使います。

それでは、始めましょう

給与テーブル salaries

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  88958 | 2002-06-22 | 9999-01-01 |
|  10002 |  72527 | 2001-08-02 | 9999-01-01 |
|  10003 |  43311 | 2001-12-01 | 9999-01-01 |
+--------+--------+------------+------------+

質問

給与が 2 番目に高い従業員の emp_no とそれに対応する給与 salary を取得してください。

思考整理

まず、給与が一番高いの従業員のsalaryを検索します
この一番目の給与を抜きにして、もう一回一番目高い給与を検索します
１番目と 2 番目に高い給与を持つ従業員は、複数存在いる可能性もあります

上記による、やり方は複数存在します。ここは、二種類を紹介します。

方法１：max() + サブクエリ

select emp_no, salary 
from salaries
where salary = 
(
  select max(salary)
  from salaries
  where salary not in ( select max(salary) from salaries )
)
order by emp_no asc
;

１番目と 2 番目に高い給与を持つ従業員は、複数存在いる時、方法２を使います。

方法２： window関数　rank()

select emp_no,salary
from
( select emp_no,salary,rank() over(order by salary desc) t
  from salaries
  where to_date='9999-01-01'
)
where t=2;

結果は以下の通り

+--------+--------+
| emp_no | salary |
+--------+--------+
|  10002 |  72527 |
+--------+--------+

DDLとDMLは以下に添付しますので、好きな方はご利用ください。

drop table if exists  `salaries` ; 
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');