데이터베이스 질문과 답변
by 쿼리냠냠 [SQL Query] [2018.11.05 15:32:00]
구루비회원님 안녕하세요. 원하는 쿼리를 만들어 보다가 자꾸 실패하여 문의드립니다.ㅠㅠ
( 쿼리는 너무어려워요..ㅠ )
*원하는 쿼리의 내용*
[1]. 월별 정산자료구하기
- '판매일자 별'로 '수수료'가 다를 수 있기때문에 '판매일자 기준으로 수수료를 가져와 SUM하는 쿼리'
테이블 구조입니다.
[1]. order Table (주문 테이블)
| calculationId(FK) | productName | price(판매가격) | date(판매일자) |
| 1 | 바나나 | 10,000 | 2018-10-01 |
| 1 | 사과 | 20,000 | 2018-10-15 |
| 1 | 수박 | 10,000 | 2018-11-01 |
| 1 | 키위 | 10,000 | 2018-11-05 |
[2]. calculation Table (정산정보 테이블)
| id | bookAt (수수료적용일) | charge (수수료) |
| 1 | 2018-09-01 | 10 |
| 1 | 2018-09-30 | 20 |
| 1 | 2018-11-05 | 10 |
*원하는 결과값*
| 판매금액 | 판매수수료 | VAT | 소계 | 정산금액 |
| 50,000 | 8,000 | 800 | 8800 | 41,200 |
판매금액 : 주문테이블의 price의 합계 ==> SUM(order.price) ( 해결함 )
판매수수료 : '판매일자 별로' 최신 적용된 정산테이블의 수수료를 가져와 SUM하는 값 ( 미해결 )
Ex) 10월달에 팔린 것들의 정산수수료는 20% => 바나나의 정산수수료 2000원 + 사과의 정산수수료 4000원 => 6000
11월달에 팔린 것들의 정산수수료는 10% => 수박의 정산수수료 1000원 + 키위의 정산수수료 1000원 => 2000
Total => 8000
VAT : 판매수수료의 10% ==> '판매수수료'를 구한다면 제가 할 수 있습니다. ( 미해결 )
소계 : 판매수수료 + VAT ==> '판매수수료'를 구한다면 제가 할 수 있습니다. ( 미해결 )
정산금액 : 판매금액 - 소계 ==> '판매수수료'를 구한다면 제가 할 수 있습니다. ( 미해결 )
개인 프로젝트(POS시스템)를 하면서 정산부분의 데이터를 뽑고싶은데 마음처럼 되질 않습니다.ㅠ
구글에서 제가 원하는 부분 ( 조인 시 최신데이터 1개의 값을 가져오기)을 보면서 응용하여 만들려고 하였지만 안되네요.;;;
서브쿼리에 limit 1을 하여 1개의 데이터를 리턴받으면 해결될 것 같았지만 mysql버전이 낮다며 지원을 안한다는 에러는 뱉더라구요 ㅠ
좋은 하루 보내세요.
WITH order_t AS
(
SELECT 1 calculationId, '바나나' productName, 10000 price, '2018-10-01' orderDate
UNION ALL SELECT 1, '사과' , 20000, '2018-10-15'
UNION ALL SELECT 1, '수박' , 10000, '2018-11-01'
UNION ALL SELECT 1, '키위' , 10000, '2018-11-05'
)
, calculation_t AS
(
SELECT 1 id, '2018-09-01' bookAt, 10 charge
UNION ALL SELECT 1, '2018-09-30', 20
UNION ALL SELECT 1, '2018-11-05', 10
)
SELECT SUM(price) AS 판매금액
, FLOOR(SUM(price * charge / 100 )) AS 판매수수료
, FLOOR(SUM(price * charge / 100 * 0.1)) AS VAT
, FLOOR(SUM(price * charge / 100 * 1.1)) AS 소계
, SUM(price)
- FLOOR(SUM(price * charge / 100 * 1.1)) AS 정산금액
FROM (SELECT price
, (SELECT c.charge
FROM calculation_t c
WHERE c.id = o.calculationId
AND c.bookAt <= o.orderDate
ORDER BY c.bookAt DESC
LIMIT 1
) charge
FROM order_t o
) a
;
) 버튼을 클릭하여 작성 하시면 됩니다.