[오라클] 소계 및 합계 내는 쿼리 모음_03

안녕하세요 다시한번 쿼리 급질문 합니다 내용을 이렇게 나와야 합니다. 작업일자       작업지시번호    모델      공정라인     대기수량 미완공수량  완공수량 2006/10/01                                          2-1 10/02                                                 2-1                                                         라인별 합계                                                         2-2                                                         2-2                                                         라인별 합계                                                          전체합계 이런 식으로 나와야 하는데 .. 지금 공정라인쪽 항목이 라인 번호 나오고 라인별 합계는 그 뒤로 나오고 전체합계가 또 그 뒤로 나옵니다. 라인이 끝나면 라인별 합계가 나와야 하는데 .... 제가 만든 쿼리는 좀 복잡하지만 이렇게 만들었습니다  SELECT  Z.SEQ        , Z.WORK_DATE        , Z.WIP_ENTITY_NAME AS WIP_ENTITY_NAME         , DECODE(Z.ROUTENAME,'라인별 소계 ',Z.ROUTENAME,     (SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = Z.ROUTENAME )) AS ROUTENAME                             , Z.MATERIALNAME     , Z.LPL_ITEM_CODE     , Z.MODEL        , Z.WAIT_QTY        , Z.PROCESS_QTY        , Z.CLOSE_QTY        , Z.START_QTY   FROM        (       SELECT 1 SEQ,    D.WORK_DATE        , D.WIP_ENTITY_NAME AS WIP_ENTITY_NAME         , D.ROUTENAME        , D.MATERIALNAME     , D.LPL_ITEM_CODE     , D.MODEL        , D.WAIT_QTY        , D.PROCESS_QTY        , D.CLOSE_QTY        , D.START_QTY  FROM      (   SELECT    SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) AS WORK_DATE         ,B.WIP_ENTITY_NAME AS WIP_ENTITY_NAME          ,(SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = A.ROUTENAME AND ROUTEVERSION = A.ROUTEVERSION) AS ROUTENAME         ,A.MATERIALNAME      ,C.LPL_ITEM_CODE      ,(SELECT MODEL FROM MATERIAL WHERE  MATERIALNAME = A.MATERIALNAME AND MATERIALVERSION = A.MATERIALVERSION ) AS MODEL         ,SUM(DECODE(WORKSTATE,'01', A.CURRQTY,0)) AS WAIT_QTY         ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY,0)) AS PROCESS_QTY         ,SUM(DECODE(WORKSTATE,'05', A.CURRQTY,0)) AS CLOSE_QTY         ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY,0)) AS START_QTY         FROM CT_LOT_MAST A         , CT_WORKINST B        , MATERIAL C    WHERE A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID          AND LOTID LIKE (SELECT X.WC_CODE                         FROM CT_CODE_TYPE X                       WHERE TYPE_CODE = 'B2'                         AND TYPE_DIV  = 'R') || '%'       AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'      AND C.MATERIALNAME = A.MATERIALNAME   AND C.MATERIALVERSION = A.MATERIALVERSION    GROUP BY      A.MATERIALNAME, A.MATERIALVERSION, A.ROUTENAME, A.ROUTEVERSION,C.LPL_ITEM_CODE          ,SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2)         ,B.WIP_ENTITY_NAME)D    ----------------------------------------------------------------------     UNION ALL   --------------------------------------------------------------------        SELECT 2 AS SEQ,    D.WORK_DATE        , D.WIP_ENTITY_NAME AS WIP_ENTITY_NAME         , '라인별 소계 'AS ROUTENAME        , D.MATERIALNAME     , D.LPL_ITEM_CODE     , D.MODEL        , D.WAIT_QTY        , D.PROCESS_QTY        , D.CLOSE_QTY        , D.START_QTY  FROM      (   SELECT    SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) AS WORK_DATE         ,B.WIP_ENTITY_NAME AS WIP_ENTITY_NAME          ,(SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = A.ROUTENAME AND ROUTEVERSION = A.ROUTEVERSION) AS ROUTENAME         ,A.MATERIALNAME      ,C.LPL_ITEM_CODE      ,(SELECT MODEL FROM MATERIAL WHERE  MATERIALNAME = A.MATERIALNAME AND MATERIALVERSION = A.MATERIALVERSION ) AS MODEL         ,SUM(DECODE(WORKSTATE,'01', A.CURRQTY,0)) AS WAIT_QTY         ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY,0)) AS PROCESS_QTY         ,SUM(DECODE(WORKSTATE,'05', A.CURRQTY,0)) AS CLOSE_QTY         ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY,0)) AS START_QTY         FROM CT_LOT_MAST A         , CT_WORKINST B        , MATERIAL C    WHERE A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID          AND LOTID LIKE (SELECT X.WC_CODE                         FROM CT_CODE_TYPE X                       WHERE TYPE_CODE = 'B2'                         AND TYPE_DIV  = 'R') || '%'       AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'      AND C.MATERIALNAME = A.MATERIALNAME   AND C.MATERIALVERSION = A.MATERIALVERSION    GROUP BY      A.MATERIALNAME, A.MATERIALVERSION, A.ROUTENAME, A.ROUTEVERSION,C.LPL_ITEM_CODE          ,SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2)         ,B.WIP_ENTITY_NAME)D               --------------------------------------------------------------------     UNION ALL   --------------------------------------------------------------------        SELECT 3 AS SEQ,    D.WORK_DATE        , D.WIP_ENTITY_NAME AS WIP_ENTITY_NAME         , '전체합계 ' AS ROUTENAME        , D.MATERIALNAME     , D.LPL_ITEM_CODE     , D.MODEL        , D.WAIT_QTY        , D.PROCESS_QTY        , D.CLOSE_QTY        , D.START_QTY  FROM      (   SELECT    SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) AS WORK_DATE         ,B.WIP_ENTITY_NAME AS WIP_ENTITY_NAME          ,(SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = A.ROUTENAME AND ROUTEVERSION = A.ROUTEVERSION) AS ROUTENAME         ,A.MATERIALNAME      ,C.LPL_ITEM_CODE      ,(SELECT MODEL FROM MATERIAL WHERE  MATERIALNAME = A.MATERIALNAME AND MATERIALVERSION = A.MATERIALVERSION ) AS MODEL         ,SUM(DECODE(WORKSTATE,'01', A.CURRQTY,0)) AS WAIT_QTY         ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY,0)) AS PROCESS_QTY         ,SUM(DECODE(WORKSTATE,'05', A.CURRQTY,0)) AS CLOSE_QTY         ,SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY,0)) AS START_QTY         FROM CT_LOT_MAST A         , CT_WORKINST B        , MATERIAL C    WHERE A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID          AND LOTID LIKE (SELECT X.WC_CODE                         FROM CT_CODE_TYPE X                       WHERE TYPE_CODE = 'B2'                         AND TYPE_DIV  = 'R') || '%'       AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'      AND C.MATERIALNAME = A.MATERIALNAME   AND C.MATERIALVERSION = A.MATERIALVERSION    GROUP BY      A.MATERIALNAME, A.MATERIALVERSION, A.ROUTENAME, A.ROUTEVERSION,C.LPL_ITEM_CODE          ,SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2)         ,B.WIP_ENTITY_NAME)D)Z     order by 4,3,2,1 좀 복잡하죠 ... 쉽게 설명하면 라인이 끝나고 고 라인에 대한 라인별 합계 그리고 라인번호 라인별 합계 ..... 모든 라인이 끝나면 최종적으로 전체합계가 나와야 하는데 ㅠㅠ 어캐 방법이 없을까요 ?

이 글에 대한 댓글이 총 4건 있습니다.

이게 맞는지는 모르겠네요

SELECT DECODE(B.SEQ,1,A.WORK_DATE) WORK_DATE,
       DECODE(B.SEQ,1,A.WIP_ENTITY_NAME) WIP_ENTITY_NAME,
       DECODE(B.SEQ,1,A.MODEL) MODEL,
       DECODE(B.SEQ,1,A.ROUTENAME,2,A.ROUTENAME||'라인 합계','전체합계') ROUTENAME,
       DECODE(B.SEQ,1,A.MATERIALNAME) MATERIALNAME,
       DECODE(B.SEQ,1,A.LPL_ITEM_CODE) LPL_ITEM_CODE,
       NVL(SUM(A.WAIT_QTY),0) WAIT_QTY
       NVL(SUM(A.PROCESS_QTY),0) PROCESS_QTY,
       NVL(SUM(A.CLOSE_QTY),0) CLOSE_QTY,
       NVL(SUM(A.START_QTY),0) START_QTY
  FROM (SELECT LEVEL SEQ
          FROM DUAL
        CONNECT BY LEVEL <= 3) B,
       (SELECT SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2) WORK_DATE,
        B.WIP_ENTITY_NAME,
        (SELECT  DESCRIPTION FROM ROUTE WHERE ROUTENAME = A.ROUTENAME AND ROUTEVERSION = A.ROUTEVERSION) ROUTENAME,
        A.MATERIALNAME,
        C.LPL_ITEM_CODE,
        C.MODEL,
        SUM(DECODE(WORKSTATE,'01', A.CURRQTY)) WAIT_QTY,
        SUM(DECODE(WORKSTATE,'02', A.CURRQTY)) PROCESS_QTY,
        SUM(DECODE(WORKSTATE,'05', A.CURRQTY)) CLOSE_QTY,
        SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY)) START_QTY
   FROM CT_LOT_MAST A, CT_WORKINST B, MATERIAL C
  WHERE A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID
    AND LOTID LIKE (SELECT X.WC_CODE
        FROM CT_CODE_TYPE X
       WHERE TYPE_CODE = 'B2'
         AND TYPE_DIV  = 'R') || '%'
    AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'
    AND C.MATERIALNAME = A.MATERIALNAME
    AND C.MATERIALVERSION = A.MATERIALVERSION
    GROUP BY SUBSTR(A.MODIFY_DATE ,1,4) || '/' ||  SUBSTR(A.MODIFY_DATE,5,2) || '/' || SUBSTR(A.MODIFY_DATE,7,2),
             B.WIP_ENTITY_NAME,A.ROUTENAME,A.ROUTEVERSION,A.MATERIALNAME,C.LPL_ITEM_CODE,C.MODEL) A
 GROUP BY DECODE(B.SEQ,1,A.WORK_DATE),
          DECODE(B.SEQ,1,A.WIP_ENTITY_NAME),
          DECODE(B.SEQ,1,A.MODEL),
          DECODE(B.SEQ,1,A.ROUTENAME,2,A.ROUTENAME||'라인 합계','전체합계'),
          DECODE(B.SEQ,1,A.MATERIALNAME),
          DECODE(B.SEQ,1,A.LPL_ITEM_CODE)
 ORDER BY 4,3,2,1

풍차님이 2006-10-30 13:22:27에 작성한 댓글입니다.
이 댓글은 2006-10-30 13:54:31에 마지막으로 수정되었습니다. Edit Ｘ

감사합니다

그런데 소계가

1-1 파트 소계

이렇게 나오는데

라인별 소게 이렇게 하는 법은 없는지 ..

서정대님이 2006-10-30 15:33:08에 작성한 댓글입니다. Edit Ｘ

SELECT DECODE(B.SEQ,1,A.WORK_DATE) WORK_DATE,
       DECODE(B.SEQ,1,A.WIP_ENTITY_NAME) WIP_ENTITY_NAME,
       DECODE(B.SEQ,1,A.MODEL) MODEL,
       DECODE(B.SEQ,1,A.ROUTENAME,2,'라인별 합계','전체합계') ROUTENAME,
       DECODE(B.SEQ,1,A.ROUTENAME,2,A.ROUTENAME||'X') ROUTENAME1, --Sort용
       DECODE(B.SEQ,1,A.MATERIALNAME) MATERIALNAME,
       DECODE(B.SEQ,1,A.LPL_ITEM_CODE) LPL_ITEM_CODE,
       NVL(SUM(A.WAIT_QTY),0) WAIT_QTY,
       NVL(SUM(A.PROCESS_QTY),0) PROCESS_QTY,
       NVL(SUM(A.CLOSE_QTY),0) CLOSE_QTY,
       NVL(SUM(A.START_QTY),0) START_QTY
  FROM (SELECT LEVEL SEQ
          FROM DUAL
        CONNECT BY LEVEL <= 3) B,
       (SELECT SUBSTR(A.MODIFY_DATE ,1,4)|| '/' ||SUBSTR(A.MODIFY_DATE,5,2)|| '/' ||SUBSTR(A.MODIFY_DATE,7,2) WORK_DATE,
               B.WIP_ENTITY_NAME,
               B.DESCRIPTION ROUTENAME,
               A.MATERIALNAME,
               C.LPL_ITEM_CODE,
               C.MODEL,
               SUM(DECODE(WORKSTATE,'01', A.CURRQTY)) WAIT_QTY,
               SUM(DECODE(WORKSTATE,'02', A.CURRQTY)) PROCESS_QTY,
               SUM(DECODE(WORKSTATE,'05', A.CURRQTY)) CLOSE_QTY,
               SUM(DECODE(WORKSTATE,'02', A.CURRQTY ,'05', A.CURRQTY)) START_QTY
          FROM D.DESCRIPTION, CT_LOT_MAST A, CT_WORKINST B, MATERIAL C
         WHERE D.ROUTENAME = A.ROUTENAME
           AND D.ROUTEVERSION = A.ROUTEVERSION
           AND A.WIP_ENTITY_ID   = B.WIP_ENTITY_ID
           AND A.LOTID LIKE (SELECT X.WC_CODE
                               FROM CT_CODE_TYPE X
                              WHERE TYPE_CODE = 'B2'
                                AND TYPE_DIV  = 'R') || '%'
           AND A.MODIFY_DATE BETWEEN '20061027000000' AND '20061027999999'
           AND C.MATERIALNAME = A.MATERIALNAME
           AND C.MATERIALVERSION = A.MATERIALVERSION
           GROUP BY SUBSTR(A.MODIFY_DATE ,1,4)|| '/' ||SUBSTR(A.MODIFY_DATE,5,2)|| '/' ||SUBSTR(A.MODIFY_DATE,7,2),
                    B.WIP_ENTITY_NAME,B.DESCRIPTION,A.MATERIALNAME,C.LPL_ITEM_CODE,C.MODEL) A
 GROUP BY DECODE(B.SEQ,1,A.WORK_DATE),DECODE(B.SEQ,1,A.WIP_ENTITY_NAME),
          DECODE(B.SEQ,1,A.MODEL),
          DECODE(B.SEQ,1,A.ROUTENAME,2,'라인별 합계','전체합계'),
          DECODE(B.SEQ,1,A.ROUTENAME,2,A.ROUTENAME||'X'),
          DECODE(B.SEQ,1,A.MATERIALNAME),DECODE(B.SEQ,1,A.LPL_ITEM_CODE)
 ORDER BY 5,3,2,1

풍차님이 2006-10-30 16:43:03에 작성한 댓글입니다. Edit Ｘ

풍차님 감사합니다.

서정대님이 2006-10-30 18:09:42에 작성한 댓글입니다. Edit Ｘ