💡 학습목표
1. 상세 보기 페이지를 위한 쿼리 확인출금 내역
1단계
-- 1 번계좌 출금 내역
select h.id, h.amount, h.w_balance,
wa.number as sender,
ifnull(h.d_account_id, 'ATM') as receiver
from history_tb as h
left join account_tb as wa
on h.w_account_id = wa.id
where h.w_account_id = 1; 2단계
select h.id, h.amount, h.w_account_id, wa.number as sender,
ifnull(da.number, 'ATM') as receiver
from history_tb as h
left join account_tb as wa
on h.w_account_id = wa.id
left join account_tb as da
on h.d_account_id = da.id
where h.w_account_id = 1;입금 내역
1단계
-- 1 번 계좌 입금 내역
select h.id, h.amount, h.d_balance, h.created_at,
da.number as recevier,
ifnull(h.w_account_id, 'ATM') as sender
from history_tb as h
left join account_tb as da
on h.d_account_id = da.id
where h.d_account_id = 1;2단계
select h.id, h.amount, h.d_balance, h.created_at, da.number as sender,
ifnull(wa.number, 'ATM') as receiver
from history_tb as h
left join account_tb as da
on h.d_account_id = da.id
left join account_tb as wa
on h.w_account_id = wa.id
where h.d_account_id = 1;-- 출금 내역
select h.id, h.amount, h.w_account_id, wa.number as sender,
ifnull(da.number, 'ATM') as receiver
from history_tb as h
left join account_tb as wa
on h.w_account_id = wa.id
left join account_tb as da
on h.d_account_id = da.id
where h.w_account_id = 1;
-- 입금 내역
select h.id, h.amount, h.d_balance, h.created_at, da.number as sender,
ifnull(wa.number, 'ATM') as receiver
from history_tb as h
left join account_tb as da
on h.d_account_id = da.id
left join account_tb as wa
on h.w_account_id = wa.id
where h.d_account_id = 1;
-- 계좌의 입 출금 내역
-- 입 출금 내역
-- (CASE WHEN THEN END 쿼리 사용해보기)
-- 1번 계좌 입 출금 내역 쿼리
select h.id, h.amount,
case when h.w_account_id = 1 then (h.w_balance)
when h.d_account_id = 1 then (h.d_balance)
end as balance,
ifnull(wa.number, 'ATM') as sender,
ifnull(da.number, 'ATM') as receiver
from history_tb as h
left join account_tb as da
on h.d_account_id = da.id
left join account_tb as wa
on h.w_account_id = wa.id
where h.d_account_id = 1 or h.w_account_id = 1;