Differences

This shows you the differences between two versions of the page.

--- kurs:connect_by_prior [2011/03/17 14:47]
mh
+++ kurs:connect_by_prior [2019/05/23 15:00] (current)
admin
@@ Line 44: / Line 44: @@
 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>

dokuwiki