데이터베이스 질문과 답변
아래에 글 올렸는데 답변이 없어서 고민고민끝에 제가 쿼리를 짜보긴 했는데요. 하고보닌깐 소팅이 문제가 되는데 이게 막막하네요..제가 짠 쿼리 올려보겠습니다.
select ka.cc, ka.dd, ka.gg, ka.hh, oc.new_category_code,
oc.new_category_degree, oc.new_category_item.code,
oc.old_category_code, oc.old_category_degree,
oc.old_category_item_code, oc.new_old_explanation
from (select aa, cc, dd, ee, gg, hh
from (
select aa, cc, dd, ee, gg, hh
from
( select category_name_code as aa,
category_degree as bb,
category_code as cc,
category_code_name as dd
from category_code
where category_name_code = '001'
and category_degree = '09') a,
( select category_name_code as ee,
category_degree as ff,
category_code as gg,
category_code_name as hh
from category_code
where category_name_code = '001'
and category_degree = '08') a,
where a.aa = b.ee(+)
and a.cc = b.gg(+)
union
select aa, cc, dd, ee, gg, hh
from
( select category_name_code as aa,
category_degree as bb,
category_code as cc,
category_code_name as dd
from category_code
where category_name_code = '001'
and category_degree = '08') a,
( select category_name_code as ee,
category_degree as ff,
category_code as gg,
category_code_name as hh
from category_code
where category_name_code = '001'
and category_degree = '09') a,
where a.ee = b.aa(+)
and a.gg = b.cc(+)
)
order by aa, gg) ka, old_connection_table oc
where ka.aa = oc.new_category_code(+)
and ka.cc = oc.new_category_item_code(+)
and ka.ee = oc.old_category_code(+)
and ka.hh = oc.old_category_item_code(+);
이렇게 짜보았는데요 일단 결과가 맞긴 한것 같은데
소팅순서에 문제가 있네요...ㅠㅠ
양쪽다 동일한 코드가 존재할경우엔 한 로우에 나오는데
서로 다른 코드는 09차인쪽 우선순위로 찍고
09차 데이타가 끝나면 오른쪽에 08차데이터를 찍는 순인데
아래에서 제가 말씀드린것처럼 코드순서대로 데이터를 출력할수는
없을까요?
고수님들에 답변이 절실할게 필요합니다..ㅠㅠ 도와주세요...
select ka.cc, ka.dd, ka.gg, ka.hh, oc.new_category_code,
oc.new_category_degree, oc.new_category_item.code,
oc.old_category_code, oc.old_category_degree,
oc.old_category_item_code, oc.new_old_explanation
from (select aa, cc, dd, ee, gg, hh
from (
select aa, cc, dd, ee, gg, hh
from
( select category_name_code as aa,
category_degree as bb,
category_code as cc,
category_code_name as dd
from category_code
where category_name_code = '001'
and category_degree = '09') a,
( select category_name_code as ee,
category_degree as ff,
category_code as gg,
category_code_name as hh
from category_code
where category_name_code = '001'
and category_degree = '08') a,
where a.aa = b.ee(+)
and a.cc = b.gg(+)
union
select aa, cc, dd, ee, gg, hh
from
( select category_name_code as aa,
category_degree as bb,
category_code as cc,
category_code_name as dd
from category_code
where category_name_code = '001'
and category_degree = '08') a,
( select category_name_code as ee,
category_degree as ff,
category_code as gg,
category_code_name as hh
from category_code
where category_name_code = '001'
and category_degree = '09') a,
where a.ee = b.aa(+)
and a.gg = b.cc(+)
)
order by aa, gg) ka, old_connection_table oc
where ka.aa = oc.new_category_code(+)
and ka.cc = oc.new_category_item_code(+)
and ka.ee = oc.old_category_code(+)
and ka.hh = oc.old_category_item_code(+);
이렇게 짜보았는데요 일단 결과가 맞긴 한것 같은데
소팅순서에 문제가 있네요...ㅠㅠ
양쪽다 동일한 코드가 존재할경우엔 한 로우에 나오는데
서로 다른 코드는 09차인쪽 우선순위로 찍고
09차 데이타가 끝나면 오른쪽에 08차데이터를 찍는 순인데
아래에서 제가 말씀드린것처럼 코드순서대로 데이터를 출력할수는
없을까요?
고수님들에 답변이 절실할게 필요합니다..ㅠㅠ 도와주세요...
by 이재현
[2011.11.08 13:25:34]
소팅문제는 나중이고 이쿼리 데이타 많이지면 힘들어 할것같은데요..
by 몽환중독자
[2011.11.08 13:40:10]
관심가져주셔서 감사합니다..
그런데 제가 튜닝은 생각도 못할 초짜라서요..
그리고 어차피 데이터가 약 3000건정도로 고정이라서 속도 부분은 신경 안썼습니다...
그런데 제가 튜닝은 생각도 못할 초짜라서요..
그리고 어차피 데이터가 약 3000건정도로 고정이라서 속도 부분은 신경 안썼습니다...
by 이재현
[2011.11.08 15:31:43]
샘플 데이타좀 만들어서 올려주세요.
WITH CATEGORY_NAME_CODE AS (
SELECT '001' CATEGORY_NAME_CODE, '08' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
UNION ALL SELECT '001' CATEGORY_NAME_CODE, '08' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
UNION ALL SELECT '001' CATEGORY_NAME_CODE, '08' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
UNION ALL SELECT '001' CATEGORY_NAME_CODE, '09' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
UNION ALL SELECT '001' CATEGORY_NAME_CODE, '09' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
)
SELECT * FROM CATEGORY_NAME_CODE
old_connection_table 이것두 올려주심 고맙구요..
WITH CATEGORY_NAME_CODE AS (
SELECT '001' CATEGORY_NAME_CODE, '08' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
UNION ALL SELECT '001' CATEGORY_NAME_CODE, '08' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
UNION ALL SELECT '001' CATEGORY_NAME_CODE, '08' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
UNION ALL SELECT '001' CATEGORY_NAME_CODE, '09' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
UNION ALL SELECT '001' CATEGORY_NAME_CODE, '09' CATEGORY_DEGREE , '?' category_code, '?_NAME' category_code_name FROM DUAL
)
SELECT * FROM CATEGORY_NAME_CODE
old_connection_table 이것두 올려주심 고맙구요..
by 이재현
[2011.11.08 20:25:56]
제가 수수께끼를 좋아라 해서요.
님이 원하시는게 카테고리명이 필요해서 조인이필요하신게 맞다면...
좀 어렵게 생각하신거 같아요..
그리고 아래 글을 보니 ..
old_connection_table 에 스키마가 올드랑 뉴를 들고 있다면..
old_connection_table 테이블이 메핑 테이블인거 같아요..
우선 제 과정이 맞다면.. 좀 어렵게 생각하신거같아요..
WITH category_code AS (
SELECT '001' category_name_code, '09' category_degree, '01' category_code, 'NEW_NAME_1' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '09' category_degree, '011' category_code, 'NEW_NAME_2' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '09' category_degree, '012' category_code, 'NEW_NAME_3' category_code_name FROM DUAL --신코드
UNION ALL SELECT '001' category_name_code, '09' category_degree, '013' category_code, 'NEW_NAME_3' category_code_name FROM DUAL --신코드
UNION ALL SELECT '001' category_name_code, '09' category_degree, '014' category_code, 'NEW_NAME_3' category_code_name FROM DUAL --신코드
UNION ALL SELECT '001' category_name_code, '09' category_degree, '015' category_code, 'NEW_NAME_3' category_code_name FROM DUAL --신코드
UNION ALL SELECT '001' category_name_code, '08' category_degree, '01' category_code, 'OLD_NAME_1' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '08' category_degree, '011' category_code, 'OLD_NAME_1' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '08' category_degree, '013' category_code, 'OLD_NAME_1' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '08' category_degree, '4' category_code, 'OLD_NAME_1' category_code_name FROM DUAL --구코드
), old_connection_table AS (
SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '01' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '01' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '011' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '011' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '012' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '013' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '013' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '014' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '015' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '015' NEW_CATEGORY_ITEM_CODE FROM DUAL
)
SELECT A.NEW_CATEGORY_ITEM_CODE, B.category_code_name, A.OLD_CATEGORY_ITEM_CODE, C.category_code_name
FROM old_connection_table A
, category_code B
, category_code C
WHERE A.OLD_CATEGORY_CODE = '001'
AND A.NEW_CATEGORY_DEGREE = '09'
AND A.NEW_CATEGORY_CODE = B.category_name_code(+)
AND A.NEW_CATEGORY_DEGREE = B.category_degree(+)
AND A.NEW_CATEGORY_ITEM_CODE = B.category_code(+)
AND A.OLD_CATEGORY_CODE = C.category_name_code(+)
AND A.OLD_CATEGORY_DEGREE = C.category_degree(+)
AND A.OLD_CATEGORY_ITEM_CODE = C.category_code(+)
ORDER BY A.NEW_CATEGORY_ITEM_CODE
님이 원하시는게 카테고리명이 필요해서 조인이필요하신게 맞다면...
좀 어렵게 생각하신거 같아요..
그리고 아래 글을 보니 ..
old_connection_table 에 스키마가 올드랑 뉴를 들고 있다면..
old_connection_table 테이블이 메핑 테이블인거 같아요..
우선 제 과정이 맞다면.. 좀 어렵게 생각하신거같아요..
WITH category_code AS (
SELECT '001' category_name_code, '09' category_degree, '01' category_code, 'NEW_NAME_1' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '09' category_degree, '011' category_code, 'NEW_NAME_2' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '09' category_degree, '012' category_code, 'NEW_NAME_3' category_code_name FROM DUAL --신코드
UNION ALL SELECT '001' category_name_code, '09' category_degree, '013' category_code, 'NEW_NAME_3' category_code_name FROM DUAL --신코드
UNION ALL SELECT '001' category_name_code, '09' category_degree, '014' category_code, 'NEW_NAME_3' category_code_name FROM DUAL --신코드
UNION ALL SELECT '001' category_name_code, '09' category_degree, '015' category_code, 'NEW_NAME_3' category_code_name FROM DUAL --신코드
UNION ALL SELECT '001' category_name_code, '08' category_degree, '01' category_code, 'OLD_NAME_1' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '08' category_degree, '011' category_code, 'OLD_NAME_1' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '08' category_degree, '013' category_code, 'OLD_NAME_1' category_code_name FROM DUAL
UNION ALL SELECT '001' category_name_code, '08' category_degree, '4' category_code, 'OLD_NAME_1' category_code_name FROM DUAL --구코드
), old_connection_table AS (
SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '01' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '01' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '011' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '011' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '012' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '013' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '013' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '014' NEW_CATEGORY_ITEM_CODE FROM DUAL
UNION ALL SELECT '001' OLD_CATEGORY_CODE, '08' OLD_CATEGORY_DEGREE, '015' OLD_CATEGORY_ITEM_CODE, '001' NEW_CATEGORY_CODE, '09' NEW_CATEGORY_DEGREE, '015' NEW_CATEGORY_ITEM_CODE FROM DUAL
)
SELECT A.NEW_CATEGORY_ITEM_CODE, B.category_code_name, A.OLD_CATEGORY_ITEM_CODE, C.category_code_name
FROM old_connection_table A
, category_code B
, category_code C
WHERE A.OLD_CATEGORY_CODE = '001'
AND A.NEW_CATEGORY_DEGREE = '09'
AND A.NEW_CATEGORY_CODE = B.category_name_code(+)
AND A.NEW_CATEGORY_DEGREE = B.category_degree(+)
AND A.NEW_CATEGORY_ITEM_CODE = B.category_code(+)
AND A.OLD_CATEGORY_CODE = C.category_name_code(+)
AND A.OLD_CATEGORY_DEGREE = C.category_degree(+)
AND A.OLD_CATEGORY_ITEM_CODE = C.category_code(+)
ORDER BY A.NEW_CATEGORY_ITEM_CODE
by 몽환중독자
[2011.11.09 13:21:19]
아 죄송합니다..제가 어제 정신없이 몰입해서 하다보니 들어와서 글 확인할 생각도 못했네요...답변이 늦어져서 죄송하고요
제 질문글에 이렇게까지 열정적으로 도움주시려는 이재현님께 거듭 감사의 말씀드립니다....감사합니다...꾸벅^^
일단 어제 대충이나마 결과가 나오게는 했는데 나중에 혹 문제의 소지가 있진 않을까라는 생각이 듭니다...ㅎㅎ
아 그리고 말씀하신대로 올드커넥션 테이블이 메핑테이블이 맞긴 한데 일단 선행조건이 category_code 라는 테이블에 차수가 09차의 테이터와 08차수의 테이터를 서로 비교해가며 한로우에 보여주려는게 목적이었습니다...^^ 제가 설명을 잘 못해서 올려놓았었나봐요^^ 아무튼 이재현님 감사드립니다...좋은 하루 되시길...^^
제 질문글에 이렇게까지 열정적으로 도움주시려는 이재현님께 거듭 감사의 말씀드립니다....감사합니다...꾸벅^^
일단 어제 대충이나마 결과가 나오게는 했는데 나중에 혹 문제의 소지가 있진 않을까라는 생각이 듭니다...ㅎㅎ
아 그리고 말씀하신대로 올드커넥션 테이블이 메핑테이블이 맞긴 한데 일단 선행조건이 category_code 라는 테이블에 차수가 09차의 테이터와 08차수의 테이터를 서로 비교해가며 한로우에 보여주려는게 목적이었습니다...^^ 제가 설명을 잘 못해서 올려놓았었나봐요^^ 아무튼 이재현님 감사드립니다...좋은 하루 되시길...^^
) 버튼을 클릭하여 작성 하시면 됩니다.