问个问题
(1)利用如下语句:
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id /*order by last_name*/ ) AS mgr_max
FROM hr.employees
WHERE MANAGER_ID=100
出来的结果
manager_id last_namesalary
mgr_max
100
Kochhar
17000.00
17000
100
De Haan
17000.00
17000
100
Raphaely
11000.00
17000
100
Weiss
8000.00
17000
100
Fripp
8200.00
17000
100
Kaufling
7900.00
17000
100
Vollman
6500.00
17000
100
Mourgos
5800.00
17000
100
Russell
14000.00
17000
100
Partners
13500.00
17000
100
Errazuriz
12000.00
17000
100
Cambrault
11000.00
17000
100
Zlotkey
10500.00
17000
100
Hartstein
13000.00
17000
(2)利用下面的语句
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id order by last_name ) AS mgr_max
FROM hr.employees
WHERE MANAGER_ID=100
出来的结果:
manager_id last_namesalary
mgr_max
100
Cambrault
11000.00
11000
100
De Haan
17000.00
17000
100
Errazuriz
12000.00
17000
100
Fripp
8200.00
17000
100
Hartstein
13000.00
17000
100
Kaufling
7900.00
17000
100
Kochhar
17000.00
17000
100
Mourgos
5800.00
17000
100
Partners
13500.00
17000
100
Raphaely
11000.00
17000
100
Russell
14000.00
17000
100
Vollman
6500.00
17000
100
Weiss
8000.00
17000
100
Zlotkey
10500.00
17000
以上的(1)和(2)差别在于(1)无order by last_name,(2)中有order by last_name,请问下,为什么加了ORDER BY 之后出来的结果有差异呢?
|