데이터베이스 질문과 답변
오라클에서(9!) 아래의 쿼리처럼.. 자재코드/ 거래처별 입고량 합계를 쿼리 했습니다.
그런데 실제로 원하는 것은 자재별 총합계량이면서 , 거래코드는 자재별 가장 많이 입고한 거래처가 대표로 나오고 ...'거래처 외' 로 표기하고 싶습니다.
어떻게 쿼리를 해야할까요.. 간단할거 같으면서도...어렵군요.
즉 아래처럼 이요..
-------------------------------
자재1 , a거래처 외 , 100
자재2 , b거래처 외 , 200
----------------------------
SELECT 자재 ,
거래처 ,
SUM( 입고량 ) AS IB_QTY
FROM MMDHP.MM_IBS A
WHERE A.IB_ILJA BETWEEN '20180201' AND '20190131'
GROUP BY 자재 , 거래처
부탁드립니다.~~
by 가람대디
[2019.07.05 14:45:40]
WITH T AS (
SELECT 'VENDOR1' AS VENDOR
,'ITEM1' AS ITEM
,100 AS INBOUND_QTY
FROM DUAL
UNION ALL
SELECT 'VENDOR2'
,'ITEM1'
,50
FROM DUAL
UNION ALL
SELECT 'VENDOR3'
,'ITEM2'
,300
FROM DUAL
UNION ALL
SELECT 'VENDOR4'
,'ITEM1'
,800
FROM DUAL
UNION ALL
SELECT 'VENDOR1'
,'ITEM2'
,80
FROM DUAL
UNION ALL
SELECT 'VENDOR5'
,'ITEM2'
,450
FROM DUAL
UNION ALL
SELECT 'VENDOR6'
,'ITEM2'
,200
FROM DUAL
)
SELECT ITEM
,MAX(DECODE(RNUM ,1 ,VENDOR || ' 외' || VENDOR_CNT)) AS VENDOR
,SUM(INBOUND_QTY) AS INBOUND_QTY
FROM (
SELECT VENDOR
,ITEM
,SUM(INBOUND_QTY) AS INBOUND_QTY
,ROW_NUMBER() OVER(PARTITION BY ITEM ORDER BY SUM(INBOUND_QTY) DESC) AS RNUM
,COUNT(VENDOR) OVER(PARTITION BY ITEM) - 1 AS VENDOR_CNT
FROM T
GROUP BY VENDOR
,ITEM
)
GROUP BY ITEM
더좋은 방법도 있을겁니다만...
by 신성철
[2019.07.05 16:01:30]
답변 감사드립니다~~~ 덕분에 해결했습니다.
by 마농
[2019.07.05 17:55:15]
SELECT item
, MIN(vendor) KEEP(DENSE_RANK LAST ORDER BY inbound_qty) ||' 외 '|| (COUNT(*)-1) vendor
, SUM(inbound_qty) inbound_qty
FROM (SELECT item, vendor, SUM(inbound_qty) inbound_qty
FROM t
GROUP BY item, vendor
)
GROUP BY item
;
) 버튼을 클릭하여 작성 하시면 됩니다.