This shows you the differences between two versions of the page.
kurs:select_mit_where_in_subquery [2014/09/10 21:22] 127.0.0.1 external edit |
kurs:select_mit_where_in_subquery [2017/03/20 11:28] admin |
||
---|---|---|---|
Line 7: | Line 7: | ||
and e.department_id = d.department_id | and e.department_id = d.department_id | ||
order by salary | order by salary | ||
+ | </code> | ||
+ | Alternativ und evtl besser verständlich | ||
+ | |||
+ | <code> | ||
+ | with max_sal_per_dep as | ||
+ | ( | ||
+ | select department_id, | ||
+ | max(salary) max_sal | ||
+ | from employees | ||
+ | group by department_id | ||
+ | ) | ||
+ | select first_name, | ||
+ | last_name, | ||
+ | salary, | ||
+ | e.department_id | ||
+ | from employees e join max_sal_per_dep m | ||
+ | on e.department_id = m.department_id and | ||
+ | e.salary = m.max_sal | ||
+ | </code> | ||
+ | |||
+ | |||
+ | |||
+ | <code> | ||
select last_name, first_name, department_id, salary from employees | select last_name, first_name, department_id, salary from employees | ||
where salary in (select min(salary) from employees group by department_id) | where salary in (select min(salary) from employees group by department_id) |