本文主要是介绍垃圾桶的空闲爆满情况/利用率分析，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
满载：
select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m
where m.WEIGHT BETWEEN 23.2265 and 27.29 order by m.DEVICECODE,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc;空闲：
select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m
where m.WEIGHT BETWEEN 0.2 and 13.745 order by m.DEVICECODE,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc;select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT,
row_number() over(partition by m.GARDENNAME,m.THROWTIME order by m.WEIGHT desc) from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m
order by m.DEVICECODE,m.SYS_KEY,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc;select m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT,
row_number() over(partition by m.GARDENNAME,m.THROWTIME order by m.WEIGHT desc) from
(select SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m
order by m.GARDENNAME,m.GARBAGETYPE,m.SYS_KEY,m.THROWTIME,m.WEIGHT asc;按照垃圾分类求重量最大值、最小值、空闲、满载：
select p.GARBAGETYPE,max(p.WEIGHT) as zd,min(p.WEIGHT) as zx,((max(p.WEIGHT)+min(p.WEIGHT))*0.5) as kx,(0.85*max(p.WEIGHT)+0.15*min(p.WEIGHT)) as mz from
(select SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) p
GROUP BY p.GARBAGETYPE;按照垃圾分类求重量满载：
select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m,
(select p.GARBAGETYPE,max(p.WEIGHT) as zd,min(p.WEIGHT) as zx,((max(p.WEIGHT)+min(p.WEIGHT))*0.5) as kx,
(0.85*max(p.WEIGHT)+0.15*min(p.WEIGHT)) as mz from
(select SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) p
GROUP BY p.GARBAGETYPE) n
where m.GARBAGETYPE = n.GARBAGETYPE and m.WEIGHT BETWEEN n.mz and n.zd order by m.DEVICECODE,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc;按照垃圾分类求重量空闲：
select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m,
(select p.GARBAGETYPE,max(p.WEIGHT) as zd,min(p.WEIGHT) as zx,((max(p.WEIGHT)+min(p.WEIGHT))*0.5) as kx,
(0.85*max(p.WEIGHT)+0.15*min(p.WEIGHT)) as mz from
(select SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) p
GROUP BY p.GARBAGETYPE) n
where m.GARBAGETYPE = n.GARBAGETYPE and m.WEIGHT BETWEEN n.zx and n.kx order by m.DEVICECODE,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc;求满载次数：
select q.DEVICECODE,q.GARDENNAME,q.GARBAGETYPE,q.THROWTIME,count(*) as mz_cs from
(select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m,
(select p.GARBAGETYPE,max(p.WEIGHT) as zd,min(p.WEIGHT) as zx,((max(p.WEIGHT)+min(p.WEIGHT))*0.5) as kx,
(0.85*max(p.WEIGHT)+0.15*min(p.WEIGHT)) as mz from
(select SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) p
GROUP BY p.GARBAGETYPE) n
where m.GARBAGETYPE = n.GARBAGETYPE and m.WEIGHT BETWEEN n.mz and n.zd order by m.DEVICECODE,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc) q
GROUP BY q.DEVICECODE,q.GARDENNAME,q.GARBAGETYPE,q.THROWTIME order by q.DEVICECODE;
求空闲次数：
select q.DEVICECODE,q.GARDENNAME,q.GARBAGETYPE,q.THROWTIME,count(*) as kx_cs from
(select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m,
(select p.GARBAGETYPE,max(p.WEIGHT) as zd,min(p.WEIGHT) as zx,((max(p.WEIGHT)+min(p.WEIGHT))*0.5) as kx,
(0.85*max(p.WEIGHT)+0.15*min(p.WEIGHT)) as mz from
(select SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) p
GROUP BY p.GARBAGETYPE) n
where m.GARBAGETYPE = n.GARBAGETYPE and m.WEIGHT BETWEEN n.zx and n.kx order by m.DEVICECODE,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc) q
GROUP BY q.DEVICECODE,q.GARDENNAME,q.GARBAGETYPE,q.THROWTIME order by q.DEVICECODE求一个月内空闲次数：
select q.DEVICECODE,q.GARDENNAME,q.GARBAGETYPE,q.THROWTIME,count(*) as kx_cs from
(select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m,
(select p.GARBAGETYPE,max(p.WEIGHT) as zd,min(p.WEIGHT) as zx,((max(p.WEIGHT)+min(p.WEIGHT))*0.5) as kx,
(0.85*max(p.WEIGHT)+0.15*min(p.WEIGHT)) as mz from
(select SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) p
GROUP BY p.GARBAGETYPE) n
where m.GARBAGETYPE = n.GARBAGETYPE and m.WEIGHT BETWEEN n.zx and n.kx order by m.DEVICECODE,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc) q
GROUP BY q.DEVICECODE,q.GARDENNAME,q.GARBAGETYPE,q.THROWTIME having substr(q.THROWTIME,1,7) = substr(TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'),1,7);求一周内空闲次数：
select q.DEVICECODE,q.GARDENNAME,q.GARBAGETYPE,q.THROWTIME,count(*) as kx_cs from
(select m.DEVICECODE,m.SYS_KEY,m.GARDENNAME,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT from
(select DEVICECODE,SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) m,
(select p.GARBAGETYPE,max(p.WEIGHT) as zd,min(p.WEIGHT) as zx,((max(p.WEIGHT)+min(p.WEIGHT))*0.5) as kx,
(0.85*max(p.WEIGHT)+0.15*min(p.WEIGHT)) as mz from
(select SYS_KEY,GARDENNAME,GARBAGETYPE,THROWTIME,to_number(WEIGHT) as WEIGHT from TFJL_COPY) p
GROUP BY p.GARBAGETYPE) n
where m.GARBAGETYPE = n.GARBAGETYPE and m.WEIGHT BETWEEN n.zx and n.kx order by m.DEVICECODE,m.GARBAGETYPE,m.THROWTIME,m.WEIGHT asc) q
GROUP BY q.DEVICECODE,q.GARDENNAME,q.GARBAGETYPE,q.THROWTIME
having trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))<=trunc(Sysdate) and trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))>= trunc(sysdate-7);求上一周的数据
Select * From TFJL_COPY a Where trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))>=trunc(Sysdate，'d')
AND trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))<= Next_day(trunc(sysdate,'d'),7);求当前日期前七天的数据
Select * From TFJL_COPY a Where trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))<=trunc(Sysdate)
and trunc(TO_DATE(THROWTIME, 'yyyy-mm-dd hh24:mi:ss'))>= trunc(sysdate-7);删除多字段重复数据
DELETE FROM TFJL_COPY_COPY a
WHERE (a.DEVICECODE, a.THROWTIME,a.GARBAGETYPE,a.WEIGHT) IN
(SELECT DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT FROM TFJL_COPY_COPY GROUP BY DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT HAVING COUNT(*) > 1)
AND ROWID NOT IN (SELECT MIN(ROWID) FROM TFJL_COPY_COPY GROUP BY DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT HAVING COUNT(*) > 1);查找多字段重复数据
SELECT * FROM TFJL_COPY_COPY a WHERE (a.DEVICECODE, a.THROWTIME,a.GARBAGETYPE,a.WEIGHT) IN (SELECT DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT
FROM TFJL_COPY_COPY GROUP BY DEVICECODE,THROWTIME,GARBAGETYPE,WEIGHT HAVING COUNT(*) > 1);
这篇关于垃圾桶的空闲爆满情况/利用率分析的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
