데이터베이스 질문과 답변
by momo [SQL Query] mysql query [2022.03.14 13:01:02]
안녕하세요. 자기 참조가 여러번 발생하는 테이블에서 특정 데이터를 찾는 쿼리를 만들고 있는데 너무 어렵게 접근하는 것 같아서 질문 드립니다.
해당 테이블 에는 아래의 그림처럼 6개의 컬럼이 존재합니다.
tpp_id 는 해당 테이블의 pk 컬럼이고, parent_tpp_id는 자신의 부모 ID를 가리키는 컬럼입니다.
예를들어서 tpp_id = 20인 행의 데이터에 수정사항이 발생하면 해당 행을 soft delete 시키고 새롭게 행을 추가합니다. 그래서 tpp_id = 38인 행이 id가 20인 것을 가리키며 새로 생성 됩니다.
tn_id는 상점 id이고, dt_id는 거래처 id 입니다. 상점에서는 보통 평범한 가격을 사용하는데, 특수한 거래처에서만 바뀌는 가격이 존재합니다. 그래서 이것을 status라는 컬럼에 G와 I라는 플래그 값으로 관리하고 있습니다.
문제는 수정사항을 관리할 때 발생합니다.
1. 기본 가격표 => 수정 발생 => 해당 기본 가격표를 참조하는 새로운 기본 가격표 생성 (tpp_id = 20 => tpp_id = 38)
2. 수정사항이 발생한 기본 가격표 => 수정 발생 => 그 가격표를 참조하는 새로운 기본 가격표 생성 (tpp_id = 38 => tpp_id = 48)
3. 기본 가격표 => 개별 가격표를 생성 (tpp_id = 38를 참조해 tpp_id = 39 개별 가격표 생성. 개별 가격표 생성시 거래처 id도 저장합니다)
4. 개별 가격표 => 수정 발생 => 해당 개별 가격표를 참조하는 새로운 개별 가격표를 생성 (tpp_id = 39를 참조해 tpp_id =40 개별 가격표 생성)
현재 어떤 거래처와 상점 id 값을 사용해 그 거래처에 적용될 기본 가격표와 개별 가격표를 뽑으려고 하고 있습니다.
만약 기본가격표 id 48이 존재하지만, 어떤 거래처에 48이 참조하는 tpp_id = 20에서 발생한 수정 이력중 하나라도 개별 가격표가 존재한다면 기본 가격표 대신 해당 개별 가격표를 뽑아야합니다.
그래서 거래처 31fe1cf0a2994e1294974b6d78d689a0 에는 사용할 수 있는 기본가격표 48이 아니라, 47이 뽑혀야 하는 상황입니다.
쿼리를 짜서 해보려고 했지만, 기본 가격표를 참조해서 개별 가격표를 만들때에 해당 개별 가격표의 부모 id는 기본 가격표를 가리키고, 개별 가격표를 수정하면 해당 개별 가격표의 부모id는 개별 가격표를 가리키는 부분이 있다보니
해당 부분의 쿼리를 작성하는 것이 쉽지 않아서 결국 코딩으로 어찌저찌 구현한 상황입니다.
어떻게 해야 이렇게 self join이 여러번 발생하는 상황에서 올바른 쿼리를 작성할 수 있을까요?
(20, null) 을 수정하여 (38, 20) 이 만들어 지는데
(38, 20) 을 수정하면 (48, 38) 이 만들어 지는게 아니라 (48, 20) 이 만들어 지네요? 최상위코드(20) 유지?
(38, 20) 의 개별 가격표를 만들면 (39, 38) 이 만들어 지네요? (39, 20) 이 만들어 지는게 아니라? 최상위코드(20) 유지 안함?
G 와 I 의 보모코드 기준이 조금 다르네요.
만약 해당 거래처의 가격표가 48 이 아닌 47 이 뽑히길 원한다면?
개별 가격표의 참조관계도 일반 가격표와 마찬가지로 최상위코드(20)를 유지 해야 맞을 듯 한데요?
WITH t AS
(
SELECT 19 tpp_id, null parent_tpp_id, '9775' tn_id, null dt_id, 'G' status, null deletedAt, 10 price
UNION ALL SELECT 20, null, '9775', null , 'G', '2022-03-01', 10
UNION ALL SELECT 21, null, '9775', null , 'G', null , 10
UNION ALL SELECT 22, null, '9775', null , 'G', null , 10
UNION ALL SELECT 23, null, '9775', null , 'G', null , 10
UNION ALL SELECT 24, null, '9775', null , 'G', null , 10
UNION ALL SELECT 25, null, '9775', null , 'G', null , 10
UNION ALL SELECT 26, null, '9775', null , 'G', null , 10
UNION ALL SELECT 27, null, '9775', null , 'G', null , 10
UNION ALL SELECT 28, null, '9775', null , 'G', null , 10
UNION ALL SELECT 29, null, '9775', null , 'G', null , 10
UNION ALL SELECT 38, 20, '9775', null , 'G', '2022-03-02', 15
UNION ALL SELECT 39, 38, '9775', '31fe', 'I', '2022-03-03', 13
UNION ALL SELECT 40, 39, '9775', '31fe', 'I', '2022-03-04', 14
UNION ALL SELECT 41, 39, '9775', '31fe', 'I', '2022-03-05', 13
UNION ALL SELECT 47, 39, '9775', '31fe', 'I', null , 17
UNION ALL SELECT 48, 20, '9775', null , 'G', null , 20
UNION ALL SELECT 49, null, '9775', null , 'G', null , 10
)
-- 매장코드와 거래처코드를 조건으로 입력받아 가격표를 조회하는 쿼리 예시입니다.
SELECT m.tn_id
, m.parent_tpp_id
, m.tpp_id m_tpp_id
, s.tpp_id s_tpp_id
, m.price 정상가격
, s.price 개별가격
, s.dt_id 거래처
FROM t m
LEFT OUTER JOIN
(SELECT a.dt_id
, a.tpp_id
, IFNULL(c.parent_tpp_id, b.parent_tpp_id) parent_tpp_id
, a.price
FROM t a
INNER JOIN t b
ON a.tn_id = b.tn_id
AND a.parent_tpp_id = b.tpp_id
LEFT OUTER JOIN t c
ON b.tn_id = c.tn_id
AND b.parent_tpp_id = c.tpp_id
WHERE a.status = 'I'
AND a.deletedAt IS NULL
AND a.tn_id = '9775'
AND a.dt_id = '31fe'
) s
ON IFNULL(m.parent_tpp_id, m.tpp_id) = s.parent_tpp_id
WHERE m.status = 'G'
AND m.deletedAt IS NULL
AND m.tn_id = '9775'
;
제가 설명을 제대로 못했던것 같은데, 마농님 쿼리 보고 감탄했습니다.
G일때랑 I일때랑 부모 코드 로직이 다른건 어쩔 수가 없더라고요... 주말에 계속 작업하다가 결국 못 만들어서 로직으로 처리했는데 덕분에 해결할 수 있었습니다.
정말 감사합니다. ㅠㅠ
) 버튼을 클릭하여 작성 하시면 됩니다.