안녕하세요!
SQL 1도 모르는 초짜입니다.
혼자서 몇일을 고민하다가 질문드려봅니다.
선배님들의 도움을 부탁드립니다.
아래 테이블은 택배운임산정의 기준이 되는 테이블입니다.
| 포장사이즈 테이블 | |
| 포장사이즈 | 포장중량 |
| 대 | 10000 |
| 중 | 5000 |
| 소 | 3000 |
아래 테이블은 주문등록을하는 테이블입니다.
*품목중량합계 : 품목중량 * 수량
*주문번호합계중량 : 주문번호별 모든 중량의 합계
| 주문테이블 | |||||
| 주문번호 | 품목 | 품목중량 | 수량 | 품목중량합계 | 주문번호합계중량 |
| 1 | 피자 | 500 | 2 | 1000 | 2000 |
| 1 | 퀘사디아 | 200 | 5 | 1000 | 2000 |
| 2 | 피자 | 500 | 4 | 2000 | 4000 |
| 2 | 퀘사디아 | 200 | 10 | 2000 | 4000 |
| 3 | 피자 | 500 | 10 | 5000 | 8500 |
| 3 | 퀘사디아 | 200 | 10 | 2000 | 8500 |
| 3 | 브리또 | 150 | 10 | 1500 | 8500 |
| 4 | 피자 | 500 | 15 | 7500 | 12750 |
| 4 | 퀘사디아 | 200 | 15 | 3000 | 12750 |
| 4 | 브리또 | 150 | 15 | 2250 | 12750 |
| 5 | 피자 | 500 | 40 | 20000 | 21500 |
| 5 | 브리또 | 150 | 10 | 1500 | 21500 |
아래 쿼리는 원하는 결과입니다.
* 주문번호합계중량을 포장사이즈 테이블에 등록된 사이즈의 중량 기준 대, 중, 소 로 분류
* 포장사이즈 테이블에 가장 높을 값을 초과 할 경우 주문번호를 대쉬를 붙여 자동생성하고 해당 수량만큼 기존의 주문번호에서 수량을 차감하여 포장사이즈를 적용합니다.
| 주문번호 | 품목 | 품목중량 | 수량 | 품목중량합계 | 주문번호합계중량 | 포장사이즈 |
| 1 | 피자 | 500 | 2 | 1000 | 2000 | 소 |
| 1 | 퀘사디아 | 200 | 5 | 1000 | 2000 | 소 |
| 2 | 피자 | 500 | 4 | 2000 | 2000 | 중 |
| 2 | 퀘사디아 | 200 | 10 | 2000 | 2000 | 중 |
| 3 | 피자 | 500 | 10 | 5000 | 8500 | 대 |
| 3 | 퀘사디아 | 200 | 10 | 2000 | 8500 | 대 |
| 3 | 브리또 | 150 | 10 | 1500 | 8500 | 대 |
| 4 | 피자 | 500 | 9 | 4500 | 9750 | 대 |
| 4 | 퀘사디아 | 200 | 15 | 3000 | 9750 | 대 |
| 4 | 브리또 | 150 | 15 | 2250 | 9750 | 대 |
| 4-1 | 피자 | 500 | 6 | 3000 | 3000 | 소 |
| 5 | 피자 | 500 | 20 | 10000 | 10000 | 대 |
| 5-1 | 피자 | 500 | 20 | 10000 | 10000 | 대 |
| 5-2 | 브리또 | 150 | 10 | 1500 | 1500 | 소 |
바쁘신 와중에 도움을 주셔서 감사합니다.
Comment 1
-
영어탈피
2023.03.11 16:06
WITH 주문테이블(주문번호, 품목, 품목중량, 수량) AS ( SELECT 1, '피자', 500, 2 UNION ALL SELECT 1, '퀘사디아', 200, 5 UNION ALL SELECT 2, '피자', 500, 4 UNION ALL SELECT 2, '퀘사디아', 200, 10 UNION ALL SELECT 3, '피자', 500, 10 UNION ALL SELECT 3, '퀘사디아', 200, 10 UNION ALL SELECT 3, '브리또', 150, 10 UNION ALL SELECT 4, '피자', 500, 15 UNION ALL SELECT 4, '퀘사디아', 200, 15 UNION ALL SELECT 4, '브리또', 150, 15 UNION ALL SELECT 5, '피자', 500, 40 UNION ALL SELECT 5, '브리또', 150, 10 ), 포장사이즈(포장사이즈, 포장중량) AS ( SELECT '대', 10000 UNION ALL SELECT '중', 5000 UNION ALL SELECT '소', 3000 ), 분류된주문 AS ( SELECT 주문번호, 품목, 품목중량, 수량, SUM(품목중량 * 수량) OVER (PARTITION BY 주문번호) AS 주문번호합계중량, 포장사이즈, 포장중량, CASE WHEN SUM(품목중량 * 수량) OVER (PARTITION BY 주문번호) <= 포장중량 THEN 포장사이즈 ELSE 포장사이즈 + '-' + CAST((SUM(품목중량 * 수량) OVER (PARTITION BY 주문번호) - 포장중량) / 포장중량 + 1 AS VARCHAR) END AS 분류 FROM 주문테이블 CROSS APPLY ( SELECT TOP 1 포장사이즈, 포장중량 FROM 포장사이즈 WHERE 포장중량 >= 품목중량 * 수량 ORDER BY 포장중량 ASC ) AS s ), 변경된주문 AS ( SELECT 분류, CASE WHEN ROW_NUMBER() OVER (PARTITION BY 분류 ORDER BY 주문번호, 품목, 품목중량, 수량) = 1 THEN 주문번호 ELSE 분류 + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY 분류 ORDER BY 주문번호, 품목, 품목중량, 수량) - 1 AS VARCHAR) END AS 주문번호, 품목, 품목중량, 수량, 주문번호합계중량, 포장사이즈 FROM 분류된주문 UNION ALL SELECT 분류 + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY 분류 ORDER BY 주문번호, 품목, 품목중량, 수량) - 1 AS VARCHAR), 주문번호, 품목, 품목중량, 수량, 주문번호합계중량 - 포장중량, '소' FROM 분류된주문 WHERE 주문번호합계중량 > 포장중량 AND 분류 NOT LIKE '%-%' AND 포장사이즈 = '소' ) SELECT 주문번호, 품목, 품목중량, 수량, 주문번호합계중량, 포장사이즈, SUM(품목중량 * 수량) OVER (PARTITION BY 분류) AS 분류합계중량 FROM 변경된주문 ORDER BY 분류, 주문번호, 품목, 품목중량, 수량 결과아래 주문번호 품목 품목중량 수량 주문번호합계중량 포장사이즈 분류합계중량 1 피자 500 2 1000 소 1000 1 퀘사디아 200 5 1000 소 1000 2 피자 500 4 2000 중 2000 2 퀘사디아 200 10 2000 중 2000 3 브리또 150 10 1500 대 8500 3 퀘사디아 200 10 2000 대 8500 3 피자 500 10 5000 대 8500 4 브리또 150 15 2250 대 9750 4 퀘사디아 200 15 3000 대 9750 4-1 피자 500 6 3000 소 3000 5 브리또 150 10 1500 소 1500 5-1 피자 500 20 10000 대 10000 5-2 브리또 150 10 1500 소 1500cmd_comment_vote_user Upvote0 Downvote0 Comment Update Delete
