데이터베이스 질문과 답변
안녕하세요.
대,중,소 분류별 데이터수를 카운트 해서 보여주려고 합니다
코드 테이블===========================
상위 코드 | 코드 | 코드레벨 | 코드명
AA | AA01 | 2 | A 코드
BB | BB01 | 2 | B 코드
CC | CC01 | 2 | C 코드
AA01 | AA01001 | 3 | AA01001 코드
AA01 | AA01002 | 3 | AA01002 코드
AA01 | AA01003 | 3 | AA01003 코드
AA01 | AA01004 | 3 | AA01004 코드
BB01 | BB01001 | 3 | BB01001 코드
BB01 | BB01002 | 3 | BB01002 코드
BB01 | BB01003 | 3 | BB01003 코드
BB01 | BB01004 | 3 | BB01004 코드
CC01 | CC01001 | 3 | CC01001 코드
CC01 | CC01002 | 3 | CC01002 코드
CC01 | CC01003 | 3 | CC01003 코드
CC01 | CC01004 | 3 | CC01004 코드
AA01001 | AA01001001 | 4 | AA01001001 코드
AA01001 | AA01001002 | 4 | AA01001002 코드
AA01001 | AA01001003 | 4 | AA01001003 코드
AA01001 | AA01001004 | 4 | AA01001004 코드
AA01001 | AA01002001 | 4 | AA01002001 코드
AA01001 | AA01002002 | 4 | AA01002002 코드
AA01001 | AA01002003 | 4 | AA01002003 코드
BB01001 | BB01001001 | 4 | BB01001001 코드
BB01001 | BB01001002 | 4 | BB01001002 코드
BB01001 | BB01001003 | 4 | BB01001003 코드
BB01001 | BB01001004 | 4 | BB01001004 코드
BB01001 | BB01002001 | 4 | BB01002001 코드
BB01001 | BB01002002 | 4 | BB01002002 코드
BB01001 | BB01002003 | 4 | BB01002003 코드
CC01001 | CC01001001 | 4 | CC01001001 코드
CC01001 | CC01001002 | 4 | CC01001002 코드
CC01001 | CC01001003 | 4 | CC01001003 코드
CC01001 | CC01001004 | 4 | CC01001004 코드
CC01001 | CC01002001 | 4 | CC01002001 코드
CC01001 | CC01002002 | 4 | CC01002002 코드
상품 테이블 =========================
상품명 | 카테고리 A 분류 | 카테고리 B 분류 | 카테고리 C 분류
ㄱ상품 | AA01001 | BB01001 | CC01002001
ㄴ상품 | AA01001 | BB01003 | CC01002002
ㄷ상품 | AA01001001 | BB01003 | CC01001003
ㄹ상품 | AA01001004 | BB01004 | CC01001002
ㅁ상품 | AA01003 | NULL | CC01002003
원하는 결과값===========================
상위 코드 | 코드 | 코드레벨 | 코드명 | 상품 개수
AA | AA01 | 2 | A 코드 | 5
BB | BB01 | 2 | B 코드 | 4
CC | CC01 | 2 | C 코드 | 5
AA01 | AA01001 | 3 | AA01001 코드 | 4
AA01 | AA01002 | 3 | AA01002 코드 | 0
AA01 | AA01003 | 3 | AA01003 코드 | 1
AA01 | AA01004 | 3 | AA01004 코드 | 0
BB01 | BB01001 | 3 | BB01001 코드 | 1
BB01 | BB01002 | 3 | BB01002 코드 | 0
BB01 | BB01003 | 3 | BB01003 코드 | 2
BB01 | BB01004 | 3 | BB01004 코드 | 1
CC01 | CC01001 | 3 | CC01001 코드 | 1
CC01 | CC01002 | 3 | CC01002 코드 | 3
CC01 | CC01003 | 3 | CC01003 코드 | 0
CC01 | CC01004 | 3 | CC01004 코드 | 0
AA01001 | AA01001001 | 4 | AA01001001 코드| 1
AA01001 | AA01001002 | 4 | AA01001002 코드| 0
AA01001 | AA01001003 | 4 | AA01001003 코드| 0
AA01001 | AA01001004 | 4 | AA01001004 코드| 1
AA01001 | AA01002001 | 4 | AA01002001 코드| 0
AA01001 | AA01002002 | 4 | AA01002002 코드| 0
AA01001 | AA01002003 | 4 | AA01002003 코드| 0
BB01001 | BB01001001 | 4 | BB01001001 코드| 1
BB01001 | BB01001002 | 4 | BB01001002 코드| 0
BB01001 | BB01001003 | 4 | BB01001003 코드| 2
BB01001 | BB01001004 | 4 | BB01001004 코드| 1
BB01001 | BB01002001 | 4 | BB01002001 코드| 0
BB01001 | BB01002002 | 4 | BB01002002 코드| 0
BB01001 | BB01002003 | 4 | BB01002003 코드| 0
CC01001 | CC01001001 | 4 | CC01001001 코드| 0
CC01001 | CC01001002 | 4 | CC01001002 코드| 1
CC01001 | CC01001003 | 4 | CC01001003 코드| 1
CC01001 | CC01001004 | 4 | CC01001004 코드| 0
CC01001 | CC01002001 | 4 | CC01002001 코드| 1
CC01001 | CC01002002 | 4 | CC01002002 코드| 2
CC01001 | CC01002003 | 4 | CC01002003 코드| 1
SELECT A.상위 코드, A.코드, A.코드레벨, A.코드명 CASE WHEN A.mcbscode = 'AA01' THEN (SELECT COUNT(상품명) FROM 상품테이블 WHERE 카테고리 A 분류 LIKE 'AA01%') WHEN A.mcbscode = 'BB01' THEN (SELECT COUNT(상품명) FROM 상품테이블 WHERE 카테고리 A 분류 LIKE 'BB01%') WHEN A.mcbscode = 'CC01' THEN (SELECT COUNT(상품명) FROM 상품테이블 WHERE 카테고리 A 분류 LIKE 'CC01%') WHEN A.mcbscode = 'AA01001' THEN (SELECT COUNT(상품명) FROM 상품테이블 WHERE 카테고리 A 분류 LIKE 'AA01001%') WHEN A.mcbscode = 'AA01002' THEN (SELECT COUNT(상품명) FROM 상품테이블 WHERE 카테고리 A 분류 LIKE 'AA01002%') ...... ELSE 0 END AS cnt FROM 코드테이블 A
이런식으로 CASE ~ WHEN ~ THEN으로 모든 코드값을 나열하기에는 너무 많아서
다른 해결법을 고민중인데 해결되지 않아서 문의드립니다.
CASE ~ WHEN ~ THEN 말고 보다 효율적(?)인 방법으로 결과값과 같은 쿼리를 뽑아낼 수 있을까요?
WITH code_t AS
(
SELECT 'AA' pcd, 'AA01' cd, 2 lv, 'A 코드' nm FROM dual
UNION ALL SELECT 'BB' , 'BB01' , 2, 'B 코드' FROM dual
UNION ALL SELECT 'CC' , 'CC01' , 2, 'C 코드' FROM dual
UNION ALL SELECT 'AA01' , 'AA01001' , 3, 'AA01001 코드' FROM dual
UNION ALL SELECT 'AA01' , 'AA01002' , 3, 'AA01002 코드' FROM dual
UNION ALL SELECT 'AA01' , 'AA01003' , 3, 'AA01003 코드' FROM dual
UNION ALL SELECT 'AA01' , 'AA01004' , 3, 'AA01004 코드' FROM dual
UNION ALL SELECT 'BB01' , 'BB01001' , 3, 'BB01001 코드' FROM dual
UNION ALL SELECT 'BB01' , 'BB01002' , 3, 'BB01002 코드' FROM dual
UNION ALL SELECT 'BB01' , 'BB01003' , 3, 'BB01003 코드' FROM dual
UNION ALL SELECT 'BB01' , 'BB01004' , 3, 'BB01004 코드' FROM dual
UNION ALL SELECT 'CC01' , 'CC01001' , 3, 'CC01001 코드' FROM dual
UNION ALL SELECT 'CC01' , 'CC01002' , 3, 'CC01002 코드' FROM dual
UNION ALL SELECT 'CC01' , 'CC01003' , 3, 'CC01003 코드' FROM dual
UNION ALL SELECT 'CC01' , 'CC01004' , 3, 'CC01004 코드' FROM dual
UNION ALL SELECT 'AA01001', 'AA01001001', 4, 'AA01001001 코드' FROM dual
UNION ALL SELECT 'AA01001', 'AA01001002', 4, 'AA01001002 코드' FROM dual
UNION ALL SELECT 'AA01001', 'AA01001003', 4, 'AA01001003 코드' FROM dual
UNION ALL SELECT 'AA01001', 'AA01001004', 4, 'AA01001004 코드' FROM dual
UNION ALL SELECT 'AA01001', 'AA01002001', 4, 'AA01002001 코드' FROM dual
UNION ALL SELECT 'AA01001', 'AA01002002', 4, 'AA01002002 코드' FROM dual
UNION ALL SELECT 'AA01001', 'AA01002003', 4, 'AA01002003 코드' FROM dual
UNION ALL SELECT 'BB01001', 'BB01001001', 4, 'BB01001001 코드' FROM dual
UNION ALL SELECT 'BB01001', 'BB01001002', 4, 'BB01001002 코드' FROM dual
UNION ALL SELECT 'BB01001', 'BB01001003', 4, 'BB01001003 코드' FROM dual
UNION ALL SELECT 'BB01001', 'BB01001004', 4, 'BB01001004 코드' FROM dual
UNION ALL SELECT 'BB01001', 'BB01002001', 4, 'BB01002001 코드' FROM dual
UNION ALL SELECT 'BB01001', 'BB01002002', 4, 'BB01002002 코드' FROM dual
UNION ALL SELECT 'BB01001', 'BB01002003', 4, 'BB01002003 코드' FROM dual
UNION ALL SELECT 'CC01001', 'CC01001001', 4, 'CC01001001 코드' FROM dual
UNION ALL SELECT 'CC01001', 'CC01001002', 4, 'CC01001002 코드' FROM dual
UNION ALL SELECT 'CC01001', 'CC01001003', 4, 'CC01001003 코드' FROM dual
UNION ALL SELECT 'CC01001', 'CC01001004', 4, 'CC01001004 코드' FROM dual
UNION ALL SELECT 'CC01001', 'CC01002001', 4, 'CC01002001 코드' FROM dual
UNION ALL SELECT 'CC01001', 'CC01002002', 4, 'CC01002002 코드' FROM dual
)
, item_t AS
(
SELECT 'ㄱ상품' item, 'AA01001' c1, 'BB01001' c2, 'CC01002001' c3 FROM dual
UNION ALL SELECT 'ㄴ상품', 'AA01001' , 'BB01003', 'CC01002002' FROM dual
UNION ALL SELECT 'ㄷ상품', 'AA01001001', 'BB01003', 'CC01001003' FROM dual
UNION ALL SELECT 'ㄹ상품', 'AA01001004', 'BB01004', 'CC01001002' FROM dual
UNION ALL SELECT 'ㅁ상품', 'AA01003' , NULL , 'CC01002003' FROM dual
)
SELECT a.pcd
, a.cd
, a.lv
, a.nm
, COUNT(b.item) cnt
FROM code_t a
LEFT OUTER JOIN item_t b
ON INSTR(b.c1, a.cd) = 1
OR INSTR(b.c2, a.cd) = 1
OR INSTR(b.c3, a.cd) = 1
GROUP BY a.pcd, a.cd, a.lv, a.nm
ORDER BY a.pcd, a.cd
;
마농님 감사합니다!! 덕분에 고민하던 쿼리가 해결되었습니다!!
답변을 받기전에
CASE ~ WHEN ~ THEN 다음으로 계층형 쿼리로 해결해보려고 했었는데
계층형 쿼리를 사용해서는 질문에 올린 결과값처럼 얻기는 힘들까요?
그리고 답변주신 쿼리에서 상품코드로 검색해서 동일하게 개수를 뽑아내려고하면
JOIN절에 상품코드로 해서 조건절 추가해주면 될까요..?
또 질문드려서 죄송합니다 ㅠ
1. 계층쿼리로도 가능은 합니다.
- 다만 이와 같이 코드값 자체가 계층형태(상위코드 포함 연결구조) 로 구성되어 있는 경우라면
- 계층쿼리 없이도 댓글과 같이 구현이 가능하므로
- 굳이 복잡하게 계층쿼리를 사용할 필요가 없습니다.
2. 상품코드 검색조건은
- 조인절(ON 절)이 아닌 조건절(WHERE 절)에서 주면 됩니다.
답변 감사합니다!!!
) 버튼을 클릭하여 작성 하시면 됩니다.