Differences

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

--- kurs:correlated_subquery_mit_having [2010/10/29 10:51]
mh created
+++ kurs:correlated_subquery_mit_having [2010/10/29 12:41]
mh
@@ Line 1: / Line 1: @@
+====== Zeige das Departement mit den meisten Angestellten pro Land =====
+<code>
+with gpl as
+(
+  SELECT DEPARTMENTS.DEPARTMENT_NAME, COUNTRIES.COUNTRY_NAME, (count(EMPLOYEES.EMPLOYEE_ID)) c
+      FROM DEPARTMENTS , LOCATIONS , COUNTRIES , EMPLOYEES
+  WHERE ( (LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID) AND
+          (COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID) AND
+          (DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID) )
+   group by DEPARTMENTS.DEPARTMENT_NAME, COUNTRIES.COUNTRY_NAME
+)
+select department_name dn, country_name, c from gpl
+where (country_name, c) in (select country_name, max(c) from gpl where gpl.country_name = country_name
+group by country_name)
+</code>
 <code>
 select d.department_name, l.city,  count(e.employee_id) as Anzahl, c.country_name

dokuwiki