Oracle 数据统计

Oracle 数据统计

select tt.日期,a.shift_name 班次,c.equ_name 机台 ,b.product_name 牌号,tt.抽检次数,tt.圆度零分,
tt.吸阻否决,tt.吸阻零分,tt.硬度否决 ,tt.硬度零分,tt.长度否决,tt.长度零分,tt.粘连线零分,tt.爆口否决,tt.外观零分
 from pub.shift a,pub.prod_material b ,pub.equipment c,
(
select check_date 日期,shift  班次,plat_num 机台,prod_id 牌号, count(*) 抽检次数, sum(decode(sign(A11205-2),1,1,0)) 圆周否决,sum(decode(sign(A11205-0.1),1,1,0))-sum(decode(sign(A11205-2),1,1,0)) 圆周零分
,sum(decode(sign(A11209-4),1,1,0)) 圆度零分,sum(decode(sign(A11207-2),1,1,0)) 吸阻否决,sum(decode(sign(A11207-0.1),1,1,0))-sum(decode(sign(A11207-2),1,1,0)) 吸阻零分
,sum(decode(sign(A11206-1),1,1,0)) 硬度否决,sum(decode(sign(A11206-0.1),1,1,0))-sum(decode(sign(A11206-1),1,1,0)) 硬度零分
,sum(decode(sign(A11204-2),1,1,0)) 长度否决,sum(decode(sign(A11204-0.1),1,1,0))-sum(decode(sign(A11204-2),1,1,0)) 长度零分
,sum(decode(sign(A11421-0.1),1,1,0)) 粘连线零分
,sum(decode(sign(A11422-0.1),1,1,0)) 爆口否决
,sum(decode(sign(A11426+A11427+A11428+A11429+A11430+A11431+A11432-2),1,1,0)) 外观零分
from qua.ct_checkinfo_zb
where check_date>to_date('2007-04-01','yyyy-mm-dd') and check_date<to_date('2007-04-20','yyyy-mm-dd')
group by check_date,plat_num,prod_id,shift
) tt
where tt.班次=a.shift_control_code and tt.机台=c.control_code and tt.牌号=b.control_code

 

select tt.日期,a.shift_name 班次,c.equ_name 机台 ,b.product_name 牌号,tt.抽检次数,tt.得分
 from pub.shift a,pub.prod_material b ,pub.equipment c,
(
select check_date 日期,shift  班次,plat_num 机台,prod_id 牌号, count(*) 抽检次数,sum(score)/ count(*) 得分

from qua.ct_checkinfo_zb
where check_date>to_date('2007-04-01','yyyy-mm-dd') and check_date<to_date('2007-04-20','yyyy-mm-dd')
group by check_date,plat_num,prod_id,shift
) tt
where tt.班次=a.shift_control_code and tt.机台=c.control_code and tt.牌号=b.control_code