oracle树形结构统计,oracle 树结构数据统计(合计)

树(三层)结构数据统计

#{policeNum}传入树结构code

/*-----三级树结构数据统计(合计)-----*/

with

/*基础数据准备*/

tp_aj as(

SELECT gt.pid, gt.type, it.name as policename,gt.policenum policenum, it.deptid,it.status

FROM (SELECT *

FROM (SELECT g.updatime，g.obj_id pid，g.gps_x x，g.gps_y y，g.creatime，g.imei，g.remark，g.policenum，g.type,

row_number() over(partition by policenum order by updatime desc) rm

FROM T_DEVICE_GPS_LAST g

WHERE g.gps_x > 0

AND g.gps_y > 0

AND g.gps_x IS NOT NULL

AND g.gps_y IS NOT NULL

AND g.policenum IS NOT NULL

-- AND g.updatime BETWEEN TO_CHAR(sysdate, 'yyyy-MM-dd') || ' 00:00:00' AND TO_CHAR(sysdate, 'yyyy-MM-dd') || ' 23:59:59'

)

WHERE rm = 1) gt,

(SELECT name, policenum, type, deptId, pdid, status