作者AmigoSafin ()
看板Database
標題[SQL ] 請教query題目
時間Wed Jul 31 05:02:57 2019
(針對
SQL 語言的問題,用這個標題。請用 Ctrl+Y 砍掉這行)
資料庫名稱: Oracle
資料庫版本:18c
內容/問題描述:
大家好 請教一題query如何解?謝謝!
Display a RANKING of the managers based on how many employees report to
them- note that a rank of 1 means that the manager manages the greatest
number of employees.
我用的是:
select m.first_name, m.last_name, COUNT(*) as NUM_EMPLOYEE from
RITDB_employee e, RANK() over (partition by (select COUNT(*) as NUM_EMPLOYEE
from RITDB_employee e INNER JOIN RITDB_manager m ON e.manager=m.employee_id
group by m.first_name, m.last_name) AS RANK) WHERE rownum=1)
錯誤為wondows function are not allowed here
Thanks a lot!!
--
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 129.21.68.100 (美國)
※ 文章網址: https://www.ptt.cc/bbs/Database/M.1564520580.A.FF6.html
→ retsamsu: put analytic functions inside aggregate functions 07/31 17:11
推 CSBS: rank over () 要放在select 08/02 01:29