oracle，sql，partition by使用

CREATE TABLE proceInfo (
ID int  NOT NULL primary key,
pName varchar  (50) NOT NULL,
pClick int  NOT NULL,
parentID int  NOT NULL)




INSERT into proceInfo (ID,pName,pClick,parentID) VALUES ( 1,'男士裤子',200,2)
INSERT into proceInfo (ID,pName,pClick,parentID) VALUES ( 2,'女士裤子',250,2)
INSERT into proceInfo (ID,pName,pClick,parentID) VALUES ( 3,'男士帽子',100,3)
INSERT into proceInfo (ID,pName,pClick,parentID) VALUES ( 4,'女士帽子',90,3)
INSERT into proceInfo (ID,pName,pClick,parentID) VALUES ( 5,'男士鞋子',350,4)

INSERT into proceInfo (ID,pName,pClick,parentID) VALUES ( 6,'女士鞋子',250,4)

使用partition by 查询出结果及方法
select * from 
(select dense_rank() over(partition by parentid order by pclick desc) my_rank,
id,pname,pclick,parentid
from proceInfo
) where my_rank=1

结果如下