67,513
社区成员
发帖
与我相关
我的任务
分享
select tmp.*,POWER(AVG(CASE WHEN D.ISCALPOINT=0 THEN POWER(D.TEST_VAL-SSS,2)ELSE NULL END),0.5) ot1 from
(SELECT Q.NAME DEVICE_NAME,T.NAME T_NAME,B.AVE,B.SD,SUM(CASE WHEN D.OUT_OF_CONTROL=1 THEN 1 ELSE 0 END)OUT_OF_CONTROL_TIME, COUNT(D.ID)TEST_TIME,D.TEST_ID,B.Batch_No,
AVG(CASE WHEN D.ISCALPOINT=0 THEN D.TEST_VAL WHEN D.ISCALPOINT IS NULL THEN D.TEST_VAL ELSE NULL END)SSS
FROM QC_QCTEST_DATA D,QC_TEST T,QC_DEVICE Q,QC_QCTEST_BATCH B
WHERE D.DEV_ID=1715
AND TO_CHAR(D.TEST_DATE_TIME,'yyyy-MM')='2013-05'
AND B.TYPE=2
AND D.BATCH_ID=B.ID
AND D.DEV_ID=Q.ID
AND D.TEST_ID=T.ID
GROUP BY D.TEST_ID,Q.NAME,T.NAME,B.AVE,B.SD,B.TYPE,B.Batch_No)tmp