kurs:correlated_subquery_mit_having
Differences
This shows you the differences between two versions of the page.
|
kurs:correlated_subquery_mit_having [2010/10/29 12:40] mh |
kurs:correlated_subquery_mit_having [2014/09/10 21:22] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | <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 | ||
| - | from departments d, employees e, LOCATIONS l, countries c | ||
| - | where | ||
| - | d.DEPARTMENT_ID = e.DEPARTMENT_ID | ||
| - | and | ||
| - | l.LOCATION_ID = d.LOCATION_ID | ||
| - | and | ||
| - | c.country_id = l.country_id | ||
| - | group by | ||
| - | d.department_name, l.city, c.country_name, e.department_id, c.country_id,d.department_id, l.location_id | ||
| - | having count(e.employee_id) = | ||
| - | ( | ||
| - | select max(count(e1.employee_id)) | ||
| - | from employees e1, departments d1, countries c1, locations l1 | ||
| - | where | ||
| - | c1.country_id = c.country_id | ||
| - | and | ||
| - | e1.department_id = d1.department_id | ||
| - | and | ||
| - | d1.location_id = l1.location_id | ||
| - | and | ||
| - | l1.country_id = c1.country_id | ||
| - | group by e1.department_id, c1.country_id | ||
| - | ) | ||
| - | </code> | ||
kurs/correlated_subquery_mit_having.txt ยท Last modified: 2014/09/10 21:22 (external edit)
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
