This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
kurs:connect_by_prior [2010/05/18 11:15] 127.0.0.1 external edit |
kurs:connect_by_prior [2019/05/23 15:00] (current) admin |
||
---|---|---|---|
Line 27: | Line 27: | ||
connect by prior employee_id = manager_id and employee_id != 108 | connect by prior employee_id = manager_id and employee_id != 108 | ||
start with employee_id = 101 | start with employee_id = 101 | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | select hd, count(hd) from ( | ||
+ | select extract(year from hire_date) hd from employees | ||
+ | --union | ||
+ | --select level+1985 i from dual connect by level < 20 | ||
+ | ) | ||
+ | group by hd | ||
+ | order by hd | ||
+ | |||
+ | |||
+ | select jahr, count(hire_date) from | ||
+ | (select level+1985 jahr from dual connect by level < 20) left join employees | ||
+ | on extract(year from hire_date) = jahr | ||
+ | group by jahr | ||
+ | order by jahr | ||
+ | </code> | ||
+ | |||
+ | <code> | ||
+ | select prior salary - salary, salary, last_name from ( | ||
+ | select rownum rn, last_name, salary from ( | ||
+ | select last_name, salary from employees order by salary desc | ||
+ | ) | ||
+ | ) connect by prior rn = rn-1 | ||
+ | start with rn = 1 | ||
+ | </code> | ||
+ | |||
+ | === Ähnliches Resultat, plain SQL === | ||
+ | |||
+ | <code> | ||
+ | select a.first_name, | ||
+ | a.last_name, a.salary, | ||
+ | max(b.salary), (max(b.salary)-a.salary) | ||
+ | from employees a join employees b | ||
+ | on a.salary > b.salary | ||
+ | and a.employee_id <> b.employee_id | ||
+ | group by a.first_name, a.last_name, a.salary | ||
+ | order by a.salary desc; | ||
+ | </code> | ||
+ | |||
+ | === Hierchie in einer Zeile === | ||
+ | <code> | ||
+ | select first_name || ' ' || last_name employee_name, | ||
+ | level, lpad('>', level * 3, '*') p, | ||
+ | sys_connect_by_path ( first_name || ' ' || last_name, '/') h | ||
+ | from employees e | ||
+ | connect by prior employee_id = manager_id | ||
+ | start with employee_id = (select employee_id from employees where manager_id is null) -- = 100 | ||
</code> | </code> |