그리고 위의 답변은

튜토리얼 포인트

에서 다음 지문을 넣고 execute 해, 오류가 나지 않은 걸 확인하고 작성한 답변입니다.

BEGIN TRANSACTION;

create table order_detail(
    idx integer primary key,
    order_idx integer,
    menu_idx integer,
    amount integer
);

create table menu(
    idx integer primary key,
    price integer
);

create table order_meta(
    idx integer primary key,
    order_idx integer,
    delivery_fee integer,
    total_price integer
);

insert into order_detail values(1,1,13,1);
insert into order_detail values(2,1,15,2);
insert into order_detail values(3,2,13,2);

insert into menu values(13,10000);
insert into menu values(14,12000);
insert into menu values(15,11000);

insert into order_meta values(1, 1, 3000, 10000);
insert into order_meta values(2, 2, 1000, 20000);
COMMIT;

select order_detail.order_idx, sum(amount*price) as amount_before_discount,
    sum(amount*price) + delivery_fee - total_price as discount_amount,
    delivery_fee, total_price
from order_meta, 
    order_detail join menu
    on order_detail.menu_idx = menu.idx
where order_meta.order_idx = order_detail.order_idx
group by order_detail.order_idx, delivery_fee, total_price;

• order_idx: order_detail 테이블의 order.idx
• amount_before_discount(할인 전 총액): order_detail, menu 테이블을 보고 적당히 곱해야함.
• discount_amount(할인 금액): 같은 테이블의 amount_before_discount + order_meta 테이블의 delivery_fee - order_meta 테이블의 total_price
• delivery_fee(배달비): order_meta 테이블의 delivery_fee
• total_price(결제금액): order_meta 테이블의 total_price

라고 생각하고 답변하겠습니다.

1. order_idx별 amount_before_discount 구하기

order_idx에 따른 amount_before_discount를 구하는 게 가장 어려워보이니, 이것 먼저 해결하고 가겠습니다.

이 값을 구하려면

1. order_detail과 menu 테이블을 menu_idx를 기준(on)으로 join 하고
2. 이를 order_idx로 group_by 해
3. order_idx와 sum(amount*price)을 해주면 될 것 같네요.

select order_idx, sum(amount*price) as amount_before_discount
from order_detail join menu
on order_detail.menu_idx = menu.idx
group by order_idx

2. + delivery fee 구하기

delivery fee는 위 쿼리에 order_meta 테이블을 추가해주면 됩니다.

select order_detail.order_idx, sum(amount*price) as amount_before_discount, delivery_fee
from order_meta, # 추가
    order_detail join menu
    on order_detail.menu_idx = menu.idx
where order_meta.order_idx = order_detail.order_idx
group by order_detail.order_idx, delivery_fee # 추가

3. + total_price 구하기

select order_detail.order_idx, sum(amount*price) as amount_before_discount, delivery_fee, total_price
from order_meta, 
    order_detail join menu
    on order_detail.menu_idx = menu.idx
where order_meta.order_idx = order_detail.order_idx
group by order_detail.order_idx, delivery_fee, total_price # 추가

4. + discount_amount 구하기

select order_detail.order_idx, sum(amount*price) as amount_before_discount,
    sum(amount*price) + delivery_fee - total_price as discount_amount, #추가
    delivery_fee, total_price
from order_meta, 
    order_detail join menu
    on order_detail.menu_idx = menu.idx
where order_meta.order_idx = order_detail.order_idx
group by order_detail.order_idx, delivery_fee, total_price