데이터베이스 질문과 답변
안녕하세요 데이터베이스를 공부하고 있는 학생입니다.!
Mysql에서 프로시저 CURSOR LOOP를 사용하는 방법이 궁금합니다.
배송테이블이 있고 휴일테이블(주말, 공휴일 포함)이 있습니다.
두 테이블을 사용하여 [정산] 테이블에 INSERT를 하고싶습니다.
[배송] 테이블에서 배송완료일 + 1을 하여 [정산] 테이블에 INSERT를 하지만
조건이 해당 정산일이 주말, 공휴일이라면 다음날 평일로 만들어 업체별로 정산일을 표시하고 싶습니다.
쿼리가 너무 어렵네요....
- [배송] 테이블
| 배송_id | 배송업체_id | 배송완료일 | 운임료 |
| 1 | 1 | 2022-04-22 | 2500 |
| 2 | 1 | 2022-04-23 | 2500 |
| 3 | 1 | 2022-05-04 | 2500 |
| 4 | 2 | 2022-05-05 | 3000 |
| 5 | 2 | 2022-09-09 | 3000 |
| 6 | 3 | 2022-09-10 | 5000 |
- [휴일] 테이블
| 휴일시작일 | 휴일종료일 | 이름 |
| 2022-04-23 | 2022-04-24 | 토요일 |
| 2022-04-24 | 2022-04-24 | 일요일 |
| 2022-05-05 | 2022-05-05 | 어린이날 |
| 2022-09-09 | 2022-09-12 | 추석 |
| 2022-09-10 | 2022-09-12 | 추석 |
| 2022-09-11 | 2022-09-12 | 추석 |
| 2022-09-12 | 2022-09-12 | 대체공휴일 |
- [정산] 테이블 (이런 형태로 INSERT가 되야함)
| 정산_id | 배송업체_id | 정산일 | 정산금액 |
| 1 | 1 | 2022-04-25 | 5000 |
| 2 | 1 | 2022-05-06 | 2500 |
| 3 | 2 | 2022-05-06 | 3000 |
| 4 | 2 | 2022-09-13 | 3000 |
| 5 | 3 | 2022-09-13 | 5000 |
결국 LOOP를 통해 [배송]테이블에서 배송완료일+1을 한 날짜가 휴일시작일과 같은 날짜라면 휴일종료일 + 1을 한다.
이게 맞을까요? 생각보다 구현하기가 어렵네요....
도움 부탁드립니다.
일자 컬럼이 varchar일경우고 date 이면 to_date 부분만 빼면 될것같습니다.
select
배송_id,
배송업체_id
,
(case
when to_date(배송완료일, 'yyyy-mm-dd') + 1 between to_date(t2.휴일시작일, 'yyyy-mm-dd') and to_date(휴일종료일, 'yyyy-mm-dd') then to_date(휴일종료일, 'yyyy-mm-dd') + 1
else to_date(배송완료일, 'yyyy-mm-dd') + 1
end ) as 정산일
, 운임료 as 정산금액
from
[배송] 테이블
left join (
select
(case when 이름 in ('토요일','일요일') then '주말' else 이름 ) 이름,
min(휴일시작일) 휴일시작일 ,
max(휴일종료일) 휴일종료일
from
[휴일] 테이블
group by
(case when 이름 in ('토요일','일요일') then '주말' else 이름 ) ) [휴일] 테이블
on
to_date(배송완료일, 'yyyy-mm-dd') + 1 between to_date(휴일시작일, 'yyyy-mm-dd') and to_date(휴일종료일, 'yyyy-mm-dd')
;
기존 질문에 답글 달았었는데요.
이번 질문은 기존 질문과 테이블 내용이 많이 다르네요.
기존엔 년,월,일 컬럼이 따로 있었는데. 지금은 날짜컬럼 하나네요.
기존엔 휴일만 딸랑 있었는데.
휴일만 있는게 아니라 주말도 들어 있고,
일자별 기간 관리 형태로 일자가 중복되어 관리되고 있네요.
현재 처럼 반정규화되어 관리되고 있다면 단순 조인으로 풀리겠는데요.
SELECT a.배송업체_id
, IFNULL(b.휴일종료일, a.배송완료일) + INTERVAL 1 DAY 정산일
, SUM(a.운임료) 정산금액
FROM 배송 a
LEFT OUTER JOIN 휴일 b
ON a.배송완료일 + INTERVAL 1 DAY = b.휴일시작일
GROUP BY a.배송업체_id
, IFNULL(b.휴일종료일, a.배송완료일) + INTERVAL 1 DAY
;
) 버튼을 클릭하여 작성 하시면 됩니다.