This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
kurs:correlated_subquery_mit_having [2010/10/29 10:51] mh created |
kurs:correlated_subquery_mit_having [2014/09/10 21:22] (current) |
||
---|---|---|---|
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> | <code> | ||
select d.department_name, l.city, count(e.employee_id) as Anzahl, c.country_name | select d.department_name, l.city, count(e.employee_id) as Anzahl, c.country_name |