sql之20再看查詢

Advanced Subqueries
'內聯檢視的應用'[@more@]

'查詢每個部門中大於本部門平均工資的員工名單及本部門的平均工資'
SQL> select a.last_name,a.salary,a.department_id,b.salavg
2 from employees a,(select department_id,avg(salary) salavg
3 from employees
4 group by department_id) b
5 where a.department_id=b.department_id
6 and a.salary>b.salavg;

LAST_NAME SALARY DEPARTMENT_ID SALAVG
---------- ---------- ------------- ----------
Hartstein 13000 20 9500
Mourgos 5800 50 3500
Lorentz 42000 60 19000
Zlotkey 10500 80 10033.3333
Abel 11000 80 10033.3333
King 24000 90 19333.3333
Higgins 12000 110 10150

7 rows selected.

'在order by子句中使用子查詢的應用'
'僱員表中有部門ID，沒有部門名稱，現在要按部門名稱對僱員進行排序'
SQL>
1 select employee_id,last_name
2 from employees e
3 order by (select department_name from departments d
4 where e.department_id=d.department_id)

EMPLOYEE_ID LAST_NAME
----------- ----------
205 Higgins
206 Gietz
200 Whalen
100 King
101 Kochhar
102 De Haan
103 Hunold
104 Ernst
107 Lorentz
201 Hartstein
202 Fay
149 Zlotkey
176 Taylor
174 Abel
124 Mourgos
141 Rajs
142 Davies
143 Matos
144 Vargas
178 Grant

20 rows selected.