树(三层)结构数据统计
#{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