Differences

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

--- kurs:connect_by_prior [2009/09/24 16:11]
mh
+++ kurs:connect_by_prior [2014/09/10 21:22]
127.0.0.1 external edit
@@ Line 1: / Line 1: @@
+<code>
+select level, lpad('-', level*2-2, '=') as position,
+       employee_id as emp_id,
+       last_name as emp_name,
+       prior employee_id as man_id,
+       prior last_name as man_name,
+       salary - prior salary as diff
+       from employees
+--where salary - prior salary > 0
+connect by prior employee_id = manager_id
+start with employee_id = 100
+</code>
+ast abschneiden
+<code>
+select level, lpad('-', level*2-2, '=') as position,
+       employee_id as emp_id,
+       last_name as emp_name,
+       prior employee_id as man_id,
+       prior last_name as man_name,
+       salary - prior salary as diff
+       from employees
+--where salary - prior salary > 0
+-- where prior employee_id != 108
+connect by prior employee_id = manager_id and employee_id != 108
+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>

dokuwiki

User Tools

Site Tools

Differences

Page Tools