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 [2014/09/10 21:22] |
||
---|---|---|---|
Line 1: | Line 1: | ||
- | <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> | + |