Hogy ne csak beleokoskodjak:
select employees.*, salaries.salary, titles.title from salaries
inner join (select emp_no, max(from_date) as from_date from salaries where salary >=120000 group by emp_no )
as maxdate on maxdate.emp_no=salaries.emp_no and maxdate.from_date = salaries.from_date
inner join (select titles.emp_no, titles.title from titles
inner join (select emp_no, max(from_date) as from_date from titles where title='staff' group by emp_no )
as maxdate on maxdate.emp_no=titles.emp_no and maxdate.from_date = titles.from_date) as titles on salaries.emp_no = titles.emp_no
inner join employees on salaries.emp_no = employees.emp_no;Eredménye:
1751 rows in set (3.525 sec)
Ha viszont limit 0,20- al futtatom, akkor 0.276 mp alatt lefut.
MariaDB [employees]> explain select employees.*, salaries.salary, titles.title from salaries inner join (select emp_no, max(from_date) as from_date from salaries where salary >=120000 group by emp_no ) as maxdat
e on maxdate.emp_no=salaries.emp_no and maxdate.from_date = salaries.from_date inner join (select titles.emp_no, titles.title from titles inner join (select emp_no, max(from_date) as from_date from titles where
title='staff' group by emp_no ) as maxdate on maxdate.emp_no=titles.emp_no and maxdate.from_date = titles.from_date) as titles on salaries.emp_no = titles.emp_no inner join employees on salaries.emp_no = employe
es.emp_no;
+------+-----------------+------------+--------+---------------+---------+---------+-------------------------------------------------------+--------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-----------------+------------+--------+---------------+---------+---------+-------------------------------------------------------+--------+-----------------------------------------------------------+
| 1 | PRIMARY | employees | ALL | PRIMARY | NULL | NULL | NULL | 299290 | |
| 1 | PRIMARY | titles | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 1 | Using index |
| 1 | PRIMARY | <derived2> | ref | key1 | key1 | 4 | employees.employees.emp_no | 2 | Using where |
| 1 | PRIMARY | salaries | eq_ref | PRIMARY | PRIMARY | 7 | employees.employees.emp_no,maxdate.from_date | 1 | |
| 1 | PRIMARY | <derived4> | ref | key2 | key2 | 8 | employees.employees.emp_no,employees.titles.from_date | 10 | |
| 4 | DERIVED | titles | index | PRIMARY | PRIMARY | 209 | NULL | 442248 | Using where; Using index; Using temporary; Using filesort |
| 2 | LATERAL DERIVED | salaries | ref | PRIMARY | PRIMARY | 4 | employees.employees.emp_no | 4 | Using where |
+------+-----------------+------------+--------+---------------+---------+---------+-------------------------------------------------------+--------+-----------------------------------------------------------+
7 rows in set (0.001 sec)
Feladat szöveges megfogalmazása: válaszd ki azokat akiknek az utolsó titulusa staff, és az utolsó fizetése >= 120000.