|
–查询近七天产线名称为101的数据，并计算完成率
1、第一种用法，这种加条件的话不知道怎么加，但是百度了大多都是用这种方法，
SELECT lineid,to_char (SYSDATE- LEVEL + 1, 'yyyy-mm-dd') worktime,target,putout,100*round(putout/target,4)||'%' "实际完成率"
from YCZJ2 t
connect by level<7
--------
2、第二种用法
select lineid,sum(putout) as pro,target,worktime,100*round(sum(putout)/avg(target),4)||'%' as "完成率"
from YCZJ2
where worktime between to_char(sysdate - 6, 'mm-dd' ）and to_char(sysdate, 'mm-dd') and lineid='101' group by worktime,target,lineid order by worktime desc
3、第三种用法
select lineid,putout,target,worktime,100*round(putout/target,4)||'%' as react
from YCZJ2
where worktime between to_char(sysdate - 7, 'mm-dd' ）and to_char(sysdate-1, 'mm-dd') and lineid='101'
----
–查询近七个小时且产线为101的所有数据，并计算出完成率
select lineid,putout,worktime,target,100*round(putout/target,4)||'%' as react
from YCZJ
where lineid='101' and worktime between '11:30-12:30' and '17:30-18:30'and month1=to_char(sysdate,'mm') and day1=to_char(sysdate-1,'dd')
group by lineid,putout,worktime,target
order by day1 desc
------------
--计算百分比
select 100*round(putout/target,4)||'%' as "完成率"from BI_YCZJ_HOUR
|