SELECT F_CODE(P_CODE) pCode, F_CODE(C_CODE) cCode, SUM(cnt1) cnt1, SUM(cnt2) cnt2, SUM(cnt3) cnt3, TRUNC(ROUND(NVL(SUM(cnt2)/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum1, TRUNC(ROUND(NVL(SUM(cnt3)/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum2, TRUNC(ROUND(NVL((SUM(cnt2)+SUM(cnt3))/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum3, SUM(cnt5) cnt5, SUM(cnt6) cnt6, TRUNC(ROUND(NVL(SUM(cnt5)/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum4, TRUNC(ROUND(NVL(SUM(cnt6)/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum5, TRUNC(ROUND(NVL((SUM(cnt5)+SUM(cnt6))/DECODE(SUM(cnt1),0,NULL,SUM(cnt1))*100,0),2),2)cntSum6, DECODE(Grouping_ID(P_CODE,C_CODE),0,1,1,2,3,3) groupId FROM( SELECT A.* , (SELECT NVL(SUM(CNT),0) FROM V_CONTRACT C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE ) CNT1 , (SELECT COUNT(K_DEPT1) FROM V_SCHEDULE_WORK C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ2 >0 AND TO_CHAR(WORK_DATE,'WW')+0 <= ( SELECT TO_CHAR(SYSDATE,'WW')-1 FROM DUAL ) ) CNT2 , (SELECT COUNT(K_DEPT1) FROM V_SCHEDULE_WORK C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ2 >0 AND TO_CHAR(WORK_DATE,'WW')+0 = ( SELECT TO_CHAR(SYSDATE,'WW')+0 FROM DUAL ) ) CNT3 , (SELECT COUNT(K_DEPT1) FROM V_SCHEDULE_WORK C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ3 >'0' AND TO_CHAR(GAETONG_DATE,'WW')+0 <= ( SELECT TO_CHAR(SYSDATE,'WW')-1 FROM DUAL ) ) CNT5 , (SELECT COUNT(K_DEPT1) FROM V_SCHEDULE_WORK C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ3 >'0' AND TO_CHAR(GAETONG_DATE,'WW')+0 = ( SELECT TO_CHAR(SYSDATE,'WW')+0 FROM DUAL ) ) CNT6 FROM ( SELECT SUBSTR( P_CODE ,0,4) P_CODE, F_CODE(SUBSTR( P_CODE ,0,4)) pCodeNm , C_CODE_NM, C_CODE FROM T_CODE WHERE P_CODE IN ('HW1010','HW2010','HW3010','HW4010') )A )B WHERE 1=1 GROUP BY ROLLUP (P_CODE,C_CODE ) ORDER BY P_CODE,C_CODE
, (SELECT NVL(SUM(CNT),0) FROM V_CONTRACT C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE ) CNT1 , (SELECT COUNT(K_DEPT1) FROM V_SCHEDULE_WORK C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ2 >0 AND TO_CHAR(WORK_DATE,'WW')+0 <= ( SELECT TO_CHAR(SYSDATE,'WW')-1 FROM DUAL ) ) CNT2 , (SELECT COUNT(K_DEPT1) FROM V_SCHEDULE_WORK C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ2 >0 AND TO_CHAR(WORK_DATE,'WW')+0 = ( SELECT TO_CHAR(SYSDATE,'WW')+0 FROM DUAL ) ) CNT3 , (SELECT COUNT(K_DEPT1) FROM V_SCHEDULE_WORK C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ3 >'0' AND TO_CHAR(GAETONG_DATE,'WW')+0 <= ( SELECT TO_CHAR(SYSDATE,'WW')-1 FROM DUAL ) ) CNT5 , (SELECT COUNT(K_DEPT1) FROM V_SCHEDULE_WORK C WHERE INST_L||'10'||SUBSTR(INST_S,7,2) = C_CODE AND SCH_SEQ3 >'0' AND TO_CHAR(GAETONG_DATE,'WW')+0 = ( SELECT TO_CHAR(SYSDATE,'WW')+0 FROM DUAL ) ) CNT6
첫번째기준에서 2번째 올린 서브쿼리 보시면 동시에 작동하는걸 그룹으로 만들어서 한번만실행할수있게 하는방법이 있을까요? 조인걸어서 다시 하기에는 좀 그래서요
함수같은걸 쓰면될까요? 속도때문에 그렇습니다
어제 질문글에 개선 쿼리 올려드렸습니다. http://gurubee.net/article/81089
그런데 어제 쿼리랑 많이 다르네요. 조건절이 누락이 많이 되어 있는데???
질문을 정확하게 해주셔야만 합니다. 중요 정보를 누락시키면 안됩니다.
답변감사합니다
주신대로 바꾸니까 훨씬 빠르네요
그런데 궁금한점이 조인걸때 왜 VC 는 안에서 VSC 는 밖에서 했는지 궁금합니다 그냥 안에서 조인 2개 걸면 안되는건가요? 다른점이있나요?
(SELECT a.kde1 , a.kde2 , a.no1 , a.p_code , a.c_code , NULLIF(a.cnt1, 0) cnt1 , COUNT(CASE WHEN vsc.sch_seq2 > 0 AND TO_CHAR(vsc.work_date , 'ww') + 0 <= TO_CHAR(sysdate, 'ww') - 1 THEN 1 END) cnt2 , COUNT(CASE WHEN vsc.sch_seq2 > 0 AND TO_CHAR(vsc.work_date , 'ww') + 0 <= TO_CHAR(sysdate, 'ww') - 0 THEN 1 END) cnt3 , COUNT(CASE WHEN vsc.sch_seq3 > 0 AND TO_CHAR(vsc.gaetong_date, 'ww') + 0 <= TO_CHAR(sysdate, 'ww') - 1 THEN 1 END) cnt5 , COUNT(CASE WHEN vsc.sch_seq3 > 0 AND TO_CHAR(vsc.gaetong_date, 'ww') + 0 <= TO_CHAR(sysdate, 'ww') - 0 THEN 1 END) cnt6 FROM (SELECT tkd.k_de1 kde1 , tkd.k_de2 kde2 , tkd.no1 , SUBSTR(p_code, 1, 4) p_code , c_code , SUM(vc.cnt) cnt1 FROM t_k_de tkd CROSS JOIN t_code tc LEFT OUTER JOIN v_contract vc ON tkd.k_de1 = vc.k_de1 AND tkd.k_de2 = vc.k_de2 AND vc.inst_l || '10' || SUBSTR(vc.inst_s, 7, 2) = c_code WHERE p_code IN ('HW1010','HW2010','HW3010','HW4010') GROUP BY tkd.k_de1, tkd.k_de2, tkd.no1, SUBSTR(p_code, 1, 4), c_code ) a LEFT OUTER JOIN v_schedule_work vsc ON a.kde1 = vsc.k_de1 AND a.kde2 = vsc.k_de2 AND vsc.inst_l || '10' || SUBSTR(vsc.inst_s, 7, 2) = a.c_code GROUP BY a.kde1, a.kde2, a.no1, a.p_code, a.c_code, a.cnt1 ) b