데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

Oracle Q&A 40217 게시물 읽기

No. 40217

쿼리 문의드립니다..생각처럼 좀 안되네여
작성자	새하정(lsmsj)	작성일	2013-08-01 10:11ⓒ 2013-08-01 10:21ⓜ	조회수	7,169

테이브은 2가지가 있습니다.

=====================================================================

주문마스터테이블 MASTER_ORDER

마스터테이블의 컬럼 : 주문번호, 고객사번호

=====================================================================

주문상세테이블DETAIL_ORDER

주문상세테이블의 컬럼 : 주문번호, 주문상세번호, 주문수량, 상품코드

=====================================================================

두테이블의 관계는 주문번호 라는 키값으로 조인이되며 하고자 하는것은

예) 연필이라는 상품을 3번이상 주문한 고객사가 얼마나 있는지에 대해 뽑아보려 합니다.

결과물은

상품코드 / 3번이상 해당상품을 구매한 업체 카운팅

샤프 / 13 -> (샤프라는 상품을 3번이상 구매했던 업체가 13곳이 있더라.)

지우개 / 4 -> (지우개라는 상품을 3번이상 구매했던 업체가 3곳이 있더라.)

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

WITH master_order(주문번호, 고객사번호) AS(

SELECT 1, '001' FROM dual UNION ALL

SELECT 2, '002' FROM dual UNION ALL

SELECT 3, '003' FROM dual UNION ALL

SELECT 4, '001' FROM dual UNION ALL

SELECT 5, '001' FROM dual UNION ALL

SELECT 6, '003' FROM dual UNION ALL

SELECT 7, '003' FROM dual

), detail_order(주문번호, 주문상세번호, 주문수량, 상품코드) AS(

SELECT 1, 100, 1, '연필' FROM dual UNION ALL

SELECT 1, 101, 1, '지우개' FROM dual UNION ALL

SELECT 2, 102, 1, '연필' FROM dual UNION ALL

SELECT 3, 103, 1, '연필' FROM dual UNION ALL

SELECT 4, 104, 1, '연필' FROM dual UNION ALL

SELECT 4, 105, 1, '지우개' FROM dual UNION ALL

SELECT 5, 106, 1, '연필' FROM dual UNION ALL

SELECT 5, 107, 1, '지우개' FROM dual UNION ALL

SELECT 6, 108, 1, '연필' FROM dual UNION ALL

SELECT 6, 109, 1, '필통' FROM dual UNION ALL

SELECT 7, 110, 1, '연필' FROM dual UNION ALL

SELECT 7, 111, 1, '필통' FROM dual

)

SELECT 상품코드, COUNT(*) "3건이상구매업체"

FROM (SELECT a.고객사번호, b.상품코드

FROM master_order a, detail_order b

WHERE a.주문번호 = b.주문번호

GROUP BY a.고객사번호, b.상품코드

HAVING COUNT(*) >= 3

)

GROUP BY 상품코드

내용은

1. 테이블 join

2. 고객사번호, 상품코드 별로 3건이상 주문한건 뽑은 후

3. 상품코드 별로 count

아린(arin76)님이 2013-08-01 11:05에 작성한 댓글입니다.
이 댓글은 2013-08-01 11:07에 마지막으로 수정되었습니다.

아린님 정말 친절한 답변 너무 감사하니다. 부가 설명까지..... (__)...

한가지만 더 추가해서...그런데.with master_order(주문번호, 고객사번호) 이렇게하면 에러가 발생하더라구요. 그래서 with master_order ( 쿼리) 이런식으로 일단 변경하여 돌려보았습니다.

일단 제가 원하는 결과치가 나오더라구요. 다시한번 감사합니다.

그런데 1가지만 더 추가를 해보려합니다. 반품에 관련된것인데요.

해당주문들중에 반품은 제외를 하고자 합니다. 반품테이블이 추가가 되겠지요.

하지만 조건은 주문수량이 반품수량보다 큰 경우는 제외를 하려합니다.

아래는 제가 조금 추가하고 변경하여 만들어봤지만 뭔가 좀 이상해..한번더 문의를 드립니다.

===========================================================================

WITH MASTER_ORDER AS
     (SELECT ORDER_CODE, CUSTOMER_CODE
        FROM TBL_ORDER
       WHERE RECORD_STATUS <> 'D'
         AND ORDER_STATUS NOT IN ('023006', '023007')           --취소 OR 보류
         AND PRODUCT_CODE IN (
                SELECT PRODUCT_UNIT_PRICE
                  FROM 상품코드_임포트테이
                            --(조회하고자 하는 삼품코드들을 임포트시킨 테이블)
                                          )),
     DETAIL_ORDER AS
     (SELECT ORDER_CODE, ORDER_DETAIL_CODE, ORDER_QTY, PRODUCT_CODE
        FROM TBL_ORDER_DETAIL
       WHERE RECORD_STATUS <> 'D'
         AND ORDER_DETAIL_STATUS NOT IN ('024007', '024008')    --취소 OR 보류
         AND PRODUCT_CODE IN (
                SELECT PRODUCT_UNIT_PRICE
                  FROM 상품코드_임포트테이
                            --(조회하고자 하는 삼품코드들을 임포트시킨 테이블)
                                          )),
     REJECT AS
     (SELECT ORDER_DETAIL_CODE, ORDER_CODE, PRODUCT_CODE, RETURN_GR_PLAN_QTY
        FROM TBL_RETURN_DETAIL_TMP
       WHERE RECORD_STATUS <> 'D'
         AND RETURN_GR_PLAN_QTY >= 1
         AND PRODUCT_CODE IN (
                SELECT PRODUCT_UNIT_PRICE
                  FROM 상품코드_임포트테이
                            --(조회하고자 하는 삼품코드들을 임포트시킨 테이블)
                                          ))
SELECT   PRODUCT_CODE, COUNT (*) "3건이상구매업체"
    FROM (SELECT   A.CUSTOMER_CODE, B.PRODUCT_CODE
              FROM MASTER_ORDER A, DETAIL_ORDER B, REJECT C
             WHERE A.ORDER_CODE = B.ORDER_CODE
               AND A.ORDER_CODE != C.ORDER_CODE
               AND B.ORDER_CODE != C.ORDER_CODE
               AND B.PRODUCT_CODE != C.PRODUCT_CODE
--AND B.ORDER_QTY > C.RETURN_GR_PLAN_QTY
          GROUP BY A.CUSTOMER_CODE, B.PRODUCT_CODE
            HAVING COUNT (*) >= 3)
   WHERE PRODUCT_CODE IN (
            SELECT PRODUCT_UNIT_PRICE
              FROM 상품코드_임포트테이블
                            --(조회하고자 하는 삼품코드들을 임포트시킨 테이블)
                                        )
GROUP BY PRODUCT_CODE;

새하정(lsmsj)님이 2013-08-01 11:43에 작성한 댓글입니다.

WITH master_order(주문번호, 고객사번호) AS(

SELECT 1, '001' FROM dual UNION ALL

SELECT 2, '002' FROM dual UNION ALL

SELECT 3, '003' FROM dual UNION ALL

SELECT 4, '001' FROM dual UNION ALL

SELECT 5, '001' FROM dual UNION ALL

SELECT 6, '003' FROM dual UNION ALL

SELECT 7, '003' FROM dual

), detail_order(주문번호, 주문상세번호, 주문수량, 상품코드) AS(

SELECT 1, 100, 2, '연필' FROM dual UNION ALL

SELECT 1, 101, 2, '지우개' FROM dual UNION ALL

SELECT 2, 102, 2, '연필' FROM dual UNION ALL

SELECT 3, 103, 2, '연필' FROM dual UNION ALL

SELECT 4, 104, 2, '연필' FROM dual UNION ALL

SELECT 4, 105, 2, '지우개' FROM dual UNION ALL

SELECT 5, 106, 2, '연필' FROM dual UNION ALL

SELECT 5, 107, 2, '지우개' FROM dual UNION ALL

SELECT 6, 108, 2, '연필' FROM dual UNION ALL

SELECT 6, 109, 2, '필통' FROM dual UNION ALL

SELECT 7, 110, 2, '연필' FROM dual UNION ALL

SELECT 7, 111, 2, '필통' FROM dual

)

, reject_order(주문번호, 주문상세번호, 반품수량, 반품코드) AS(

SELECT 4, 104, 2, '연필' FROM dual

)

SELECT 상품코드, COUNT(*) "3건이상구매업체"

FROM (SELECT a.고객사번호, b.상품코드

FROM master_order a

, (SELECT b.주문번호, b.주문상세번호, b.상품코드

FROM detail_order b, reject_order c

WHERE b.주문번호 = c.주문번호(+)

AND b.주문상세번호 = c.주문상세번호(+)

AND b.주문수량 - NVL(c.반품수량, 0) > 0

) b

WHERE a.주문번호 = b.주문번호

GROUP BY a.고객사번호, b.상품코드

HAVING COUNT(*) >= 3

)

GROUP BY 상품코드

주문테이블 과 반품테이블을 join 해서 수량이 0 보다 큰것을 구한다음

master_order 테이블과 join 합니다.

-- scalar subquery

SELECT 상품코드, COUNT(*) "3건이상구매업체"

FROM (SELECT 고객사번호, 상품코드

FROM (SELECT a.주문번호, a.고객사번호, b. 상품코드, b.주문수량

, NVL((SELECT c.반품수량

FROM reject_order c

WHERE b.주문번호 = c.주문번호

AND b.주문상세번호 = c.주문상세번호),0) 반품수량

FROM master_order a

, detail_order b

WHERE a.주문번호 = b.주문번호

)

WHERE 주문수량 > 반품수량

GROUP BY 고객사번호, 상품코드

HAVING COUNT(*) >= 3

)

GROUP BY 상품코드

아린(arin76)님이 2013-08-01 12:21에 작성한 댓글입니다.
이 댓글은 2013-08-01 12:49에 마지막으로 수정되었습니다.

SELECT PRODUCT_CODE, COUNT(*) "3건이상구매업체"

FROM (SELECT CUSTOMER_CODE, PRODUCT_CODE

FROM (SELECT A.ORDER_CODE, A.CUSTOMER_CODE

, B.PRODUCT_CODE, B.ORDER_QTY

, NVL((SELECT c.RETURN_GR_PLAN_QTY

FROM TBL_RETURN_DETAIL_TMP C

WHERE c.ORDER_CODE = b.ORDER_CODE

AND c.RECORD_STATUS <> 'D'

AND c.ORDER_DETAIL_CODE = b.ORDER_DETAIL_CODE

AND c.PRODUCT_CODE = b.PRODUCT_CODE), 0)

RETURN_GR_PLAN_QTY

FROM TBL_ORDER A

, TBL_ORDER_DETAIL B

WHERE A.ORDER_CODE = B.ORDER_CODE

AND A.RECORD_STATUS <> 'D'

AND A.ORDER_STATUS NOT IN ('023006', '023007')

AND B.RECORD_STATUS <> 'D'

AND B.ORDER_DETAIL_STATUS NOT IN ('024007', '024008')

AND A.PRODUCT_CODE IN (조회될 상품 코드)

)

WHERE ORDER_QTY > RETURN_GR_PLAN_QTY

GROUP BY CUSTOMER_CODE, PRODUCT_CODE

HAVING COUNT(*) >= 3

)

GROUP BY PRODUCT_CODE

아린(arin76)님이 2013-08-01 13:55에 작성한 댓글입니다.
이 댓글은 2013-08-01 13:57에 마지막으로 수정되었습니다.

아린님 제 최종적인 쿼리는 아래와 같이 작성하였습니다. 혹시 좀더 다듬을 부분이있다면

한수 부탁드립니다 (__)..

==============================================================

WITH MASTER_ORDER AS(
SELECT ORDER_CODE , CUSTOMER_CODE FROM TBL_ORDER
WHERE RECORD_STATUS <> 'D'
AND ORDER_STATUS NOT IN ('023006', '023007')
),
DETAIL_ORDER AS(
SELECT ORDER_CODE, ORDER_DETAIL_CODE, ORDER_QTY, PRODUCT_CODE
FROM TBL_ORDER_DETAIL
WHERE RECORD_STATUS <> 'D'
AND ORDER_DETAIL_STATUS NOT IN ('024007', '024008')
AND PRODUCT_CODE IN
(
SELECT PRODUCT_UNIT_PRICE FROM MOOK_TEMP01
)
)
SELECT   PRODUCT_CODE, COUNT (*) "3건이상구매업체"
    FROM (SELECT   A.CUSTOMER_CODE, B.PRODUCT_CODE
              FROM MASTER_ORDER A, DETAIL_ORDER B
             WHERE A.ORDER_CODE = B.ORDER_CODE
             AND NOT EXISTS
             (
             SELECT C.ORDER_DETAIL_CODE FROM TBL_RETURN_DETAIL_TMP C
             WHERE C.ORDER_DETAIL_CODE = B.ORDER_DETAIL_CODE
             AND C.PRODUCT_CODE = B.PRODUCT_CODE
             AND NVL(C.RETURN_GR_PLAN_QTY, 0) = B.ORDER_QTY
             )
          GROUP BY A.CUSTOMER_CODE, B.PRODUCT_CODE
            HAVING COUNT (*) >= 3)
GROUP BY PRODUCT_CODE

새하정(lsmsj)님이 2013-08-01 16:23에 작성한 댓글입니다.
이 댓글은 2013-08-01 16:24에 마지막으로 수정되었습니다.

-- 지나친 With절 사용이 좋지만은 않아요. --
-- With 절 제거하고 조인으로만 풀었습니다.
SELECT product_code
     , COUNT(*) cnt
FROM (
        SELECT a.customer_code, b.product_code
          FROM tbl_order a
             , tbl_order_detail b
             , mook_temp01 c
             , tbl_return_detail_tmp d
         WHERE a.order_code        = b.order_code               -- a-b조인
           AND b.product_code      = c.product_unit_price       -- b-c조인
           AND b.order_code        = d.order_code        (+)    -- b-d조인1
           AND b.order_detail_code = d.order_detail_code (+)    -- b-d조인2
           AND b.product_code      = d.product_code      (+)    -- b-d조인3
           AND b.order_qty         = d.return_gr_plan_qty(+)    -- b-d조인4
           AND a.order_status NOT IN ('023006', '023007')       --취소,보류
           AND b.order_detail_status NOT IN ('024007', '024008')--취소,보류
           AND a.record_status    != 'D'
           AND b.record_status    != 'D'
         --AND d.record_status(+) != 'D'
           AND d.order_code IS NULL -- 아우터조인후 널체크(반품 아닌거만)
         GROUP BY a.customer_code, b.product_code
         HAVING COUNT(*) >= 3
        )
GROUP BY product_code
;

마농(manon94)님이 2013-08-01 17:16에 작성한 댓글입니다.

어쿠..마농님 감사합니다.. 지나친 with는 자제하도록 하겠습니다.!!

새하정(lsmsj)님이 2013-08-01 17:32에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
40220	(꼭 부탁)local index partition unique질문입니다. [1]	초봉~	2013-08-05	5932
40219	이런 결과값을 구할수 있을까요? [6]	이선	2013-08-05	6879
40218	마농님 다시 부탁드립니다! [2]	김정훈	2013-08-02	6641
40217	쿼리 문의드립니다..생각처럼 좀 안되네여 [7]	새하정	2013-08-01	7169
40216	explain plan의 값 확인 [1]	초보디비	2013-07-31	6856
40215	oracle에서 update 시 rownum 사용 [1]	어렵다	2013-07-31	6630
40214	쿼리질문 올려봅니다~ [2]	최규운	2013-07-31	6252