トピック ：入社時期が3番目に遅い社員のすべての情報を見つける
従業員テーブル employees の情報は以下のように:
+--------+------------+------------+-----------+--------+------------+---------+
| emp_no | birth_date | first_name | last_name | gender | hire_date | row_num |
+--------+------------+------------+-----------+--------+------------+---------+
| 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | 1 |
| 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | 2 |
| 10011 | 1953-11-07 | Mary | Sluis | F | 1989-09-12 | 2 |
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | 3 |
| 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | 4 |
| 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | 5 |
| 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | 6 |
| 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | 7 |
| 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | 8 |
| 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | 9 |
| 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | 10 |
+--------+------------+------------+-----------+--------+------------+---------+問題：
入社時期が3番目に遅い社員のすべての情報を見つけてください。
上記の例の出力は次のようになります。
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date |
+--------+------------+------------+-----------+--------+------------+
| 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+解答：
思考：
同日に入社した社員もいるかもしれないので、入社時期が3番目に遅い社員が複数いるかもしれません。
方法１：サブクエリ
select * from employees
where hire_date =
(
select distinct hire_date
from employees
order by hire_date desc
limit 1 offset 2
);方法２：window関数 DENSE_RANK() ランキング用のwindow関数の説明
select t_rank.emp_no, t_rank.birth_date, t_rank.first_name, t_rank.last_name, t_rank.gender, t_rank.hire_date
from
(
select emp_no, birth_date, first_name, last_name, gender, hire_date,
DENSE_RANK() OVER(order by hire_date desc) as row_num
from employees
) t_rank
where t_rank.row_num = 3;DDLとDMLは以下に添付するので、好きな方はご利用ください。
drop table if exists `employees` ;
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1989-09-12');
コメント