데이터베이스 질문과 답변
안녕하세요
DB는 PostgreSQL이구요
아래와 같이 테이블은 두개로 구성되어져 있습니다.
A테이블에 B테이블을 매칭시키고 각 칼럼중에 어느 칼럼이 매칭되었는지 플래그 정보를 가지는 결과를 가지는 C테이블을 만들고자 합니다.
데이타는 다음과 같습니다.
Table A 8개칼럼
통합ID 성 이름 우편번호 주소 집전화번호 핸드폰번호 이메일
c_id f_name g_name zipcode address home_tel cel_phone email
Id1 김 희숙 111-111 서울특별시 02-1111-1111 010-1111-1111 1111@gmail.com
Id2 최 성희 222-222 서울특별시 02-2222-2222 010-2222-2222 222@gmail.com
Id3 강 성수 333-333 경기도 032-333-3333 010-3333-3333 333@gmail.com
Id4 조 환섭 444-444 강원도 0391-444-4444 010-4444-4444 444@gmail.com
Table B 7개칼럼
성 이름 우편번호 주소 집전화번호 핸드폰번호 이메일
f_name g_name zipcode address home_tel cel_phone email
김 희숙 111-111 서울특별시 02-1111-1111 010-1111-1111 1111@gmail.com
홍 길동 222-222 전라도 055-555-5555 010-5555-5555 5555@gmail.com
최 성희 222-666 서울특별시 02-6666-6666 010-2222-2222 5555@gmail.com
결과값은 다음과 같습니다.
C Table 15개칼럼
c_id f_name g_name zipcode address home_tel cel_phone email f_name_flg g_name_flg zipcode_flg address_flg home_tel_flg cel_phone_flg email_flg
Id1 김 희숙 111-111 서울특별시 02-1111-1111 010-1111-1111 1111@gmail.com 1 1 1 1 1 1
Id5 홍 길동 222-222 전라도 055-555-5555 010-5555-5555 5555@gmail.com 0 0 0 0 0 0
Id6 최 성희 222-666 서울특별시 02-6666-6666 010-2222-2222 5555@gmail.com 1 0 1 0 1 0
즉 이름을 기준으로 각 칼럼의 값이 같으면 1을 틀리면 0을 붙이는 방법입니다. c_id는 플래그값에 0이 한개라도 있다면 증가하면 되구요
Sql로 처리가 가능한가요?
WITH t_a(c_id, f_name, g_name, zipcode, address, home_tel, cel_phone, email) AS
(
SELECT 'Id1', '김', '희숙', '111-111', '서울특별시', '02-1111-1111' , '010-1111-1111', '1111@gmail.com' FROM dual UNION ALL
SELECT 'Id2', '최', '성희', '222-222', '서울특별시', '02-2222-2222' , '010-2222-2222', '222@gmail.com' FROM dual UNION ALL
SELECT 'Id3', '강', '성수', '333-333', '경기도' , '032-333-3333' , '010-3333-3333', '333@gmail.com' FROM dual UNION ALL
SELECT 'Id4', '조', '환섭', '444-444', '강원도' , '0391-444-4444', '010-4444-4444', '444@gmail.com' FROM dual
)
, t_b(f_name, g_name, zipcode, address, home_tel, cel_phone, email) AS
(
SELECT '김', '희숙', '111-111', '서울특별시', '02-1111-1111' , '010-1111-1111', '1111@gmail.com' FROM dual UNION ALL
SELECT '홍', '길동', '222-222', '전라도' , '055-555-5555' , '010-5555-5555', '5555@gmail.com' FROM dual UNION ALL
SELECT '최', '성희', '222-666', '서울특별시', '02-6666-6666' , '010-2222-2222', '5555@gmail.com' FROM dual
)
SELECT DECODE(flg, 1, c_id, 'id' ||
(max_id + ROW_NUMBER() OVER(PARTITION BY flg ORDER BY c_id, f_name, g_name))) c_id
, f_name, g_name, zipcode, address, home_tel, cel_phone, email
, f_name_flg, g_name_flg, zipcode_flg, address_flg, home_tel_flg, cel_phone_flg
FROM (SELECT a.c_id
, b.f_name, b.g_name, b.zipcode, b.address, b.home_tel, b.cel_phone, b.email
, CASE WHEN b.f_name = a.f_name THEN 1 ELSE 0 END f_name_flg
, CASE WHEN b.g_name = a.g_name THEN 1 ELSE 0 END g_name_flg
, CASE WHEN b.zipcode = a.zipcode THEN 1 ELSE 0 END zipcode_flg
, CASE WHEN b.address = a.address THEN 1 ELSE 0 END address_flg
, CASE WHEN b.home_tel = a.home_tel THEN 1 ELSE 0 END home_tel_flg
, CASE WHEN b.cel_phone = a.cel_phone THEN 1 ELSE 0 END cel_phone_flg
, CASE WHEN b.f_name = a.f_name
AND b.g_name = a.g_name
AND b.zipcode = a.zipcode
AND b.address = a.address
AND b.home_tel = a.home_tel
AND b.cel_phone = a.cel_phone
THEN 1 ELSE 0 END flg
, (SELECT MAX(TO_NUMBER(SUBSTR(c_id, 3))) FROM t_a) max_id
FROM t_b b
LEFT OUTER JOIN t_a a
ON a.f_name = b.f_name
AND a.g_name = b.g_name
)
ORDER BY c_id
;
마농님 정말 감사드립니다.
일이 있어서 이제서야 확인했네요. 정말 대단하세요. 다시한번 감사합니다.
참고로 Postgresql로 변경했을때의 코드를 올립니다.
WITH t_a(c_id, f_name, g_name, zipcode, address, home_tel, cel_phone, email) AS
(
SELECT 'Id1', '김', '희숙', '111-111', '서울특별시', '02-1111-1111' , '010-1111-1111', '1111@gmail.com' FROM dual UNION ALL
SELECT 'Id2', '최', '성희', '222-222', '서울특별시', '02-2222-2222' , '010-2222-2222', '222@gmail.com' FROM dual UNION ALL
SELECT 'Id3', '강', '성수', '333-333', '경기도' , '032-333-3333' , '010-3333-3333', '333@gmail.com' FROM dual UNION ALL
SELECT 'Id4', '조', '환섭', '444-444', '강원도' , '0391-444-4444', '010-4444-4444', '444@gmail.com' FROM dual
)
, t_b(f_name, g_name, zipcode, address, home_tel, cel_phone, email) AS
(
SELECT '김', '희숙', '111-111', '서울특별시', '02-1111-1111' , '010-1111-1111', '1111@gmail.com' FROM dual UNION ALL
SELECT '홍', '길동', '222-222', '전라도' , '055-555-5555' , '010-5555-5555', '5555@gmail.com' FROM dual UNION ALL
SELECT '최', '성희', '222-666', '서울특별시', '02-6666-6666' , '010-2222-2222', '5555@gmail.com' FROM dual
)
SELECT --DECODE(flg, 1, c_id, 'id' ||(max_id + ROW_NUMBER() OVER(PARTITION BY flg ORDER BY c_id, f_name, g_name))) c_id,
CASE flg WHEN 1 THEN c_id ELSE 'id' || (max_id + ROW_NUMBER() OVER(PARTITION BY flg ORDER BY c_id, f_name)) END c_id
,f_name, g_name, zipcode, address, home_tel, cel_phone, email, f_name_flg, g_name_flg, zipcode_flg, address_flg, home_tel_flg, cel_phone_flg
FROM (SELECT a.c_id
, b.f_name, b.g_name, b.zipcode, b.address, b.home_tel, b.cel_phone, b.email
, CASE WHEN b.f_name = a.f_name THEN 1 ELSE 0 END f_name_flg
, CASE WHEN b.g_name = a.g_name THEN 1 ELSE 0 END g_name_flg
, CASE WHEN b.zipcode = a.zipcode THEN 1 ELSE 0 END zipcode_flg
, CASE WHEN b.address = a.address THEN 1 ELSE 0 END address_flg
, CASE WHEN b.home_tel = a.home_tel THEN 1 ELSE 0 END home_tel_flg
, CASE WHEN b.cel_phone = a.cel_phone THEN 1 ELSE 0 END cel_phone_flg
, CASE WHEN b.f_name = a.f_name
AND b.g_name = a.g_name
AND b.zipcode = a.zipcode
AND b.address = a.address
AND b.home_tel = a.home_tel
AND b.cel_phone = a.cel_phone
THEN 1 ELSE 0 END flg
, (SELECT MAX(TO_NUMBER(SUBSTR(c_id, 3), '000')) FROM t_a) max_id
FROM t_b b
LEFT OUTER JOIN t_a a
ON a.f_name = b.f_name
AND a.g_name = b.g_name
) A
ORDER BY c_id
;
) 버튼을 클릭하여 작성 하시면 됩니다.