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 [2012/10/04 14:57] mh |
kurs:connect_by_prior [2019/05/23 15:00] (current) admin |
||
---|---|---|---|
Line 53: | Line 53: | ||
) connect by prior rn = rn-1 | ) connect by prior rn = rn-1 | ||
start with 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> |