2000버전의 MSSQL Server는 이러한 구현을 하려면 매우 우울했었다. 많은 방법을 생각해보았지만 커서만한 솔루션이 없었으니까 말이다. 어찌되었던 2005 버전에서는 CTE를 사용하는 재귀쿼리를 이용하여 선입선출 및 차감 Row단위의 접근이 어느 정도 가능해졌다. 뭐 집합적으로 가능해졌다고 할 수도 있겠으나 집합적이다 라고 말하기도 조금 뭣하다. 어찌되었건 다음과 같이 구현해 보았다.
use tempdb
go
if object_id('bill_policy') is not null
drop table bill_policy
create table bill_policy(
charge_type varchar(20)
, calc_rank tinyint
)
if object_id('bill') is not null
drop table bill
create table bill(
seq int identity(1,1) primary key
, cust_id varchar(20)
, cash int
, charge_type varchar(20)
, create_dt datetime
)
insert bill_policy values('이벤트', 1)
insert bill_policy values('상품권', 2)
insert bill_policy values('실캐시', 3)
insert bill_policy values('포인트', 4)
insert bill values('yasicom', 500, '이벤트', '20070811')
insert bill values('yasicom', 5000, '실캐시', '20070701')
insert bill values('yasicom', 10, '포인트', '20070811')
insert bill values('yasicom', 2000, '이벤트', '20070912')
insert bill values('yasicom', 500, '상품권', '20070830')
insert bill values('yasicom', 10000, '실캐시', '20070613')
select * from bill
select * from bill_policy
--만약 1,7000원 짜리item을 구매한다면..
declare @billlog table
(
seq int
, cust_id varchar(20)
, cash int
, remain_cash int
, deduction_cash int
, charge_type varchar(20)
, calc_rank int
, create_dt datetime
)
declare
@price int
, @cust_id varchar(20);
set @cust_id = 'yasicom'
set @price = 17000; --세미콜론(;)을 꼭 찍어줘야 한다. 그렇지 않으면 문법에러라고 뻘건 글씨봐야 한다.
if (select isnull(sum(cash), 0) from bill where cust_id = @cust_id) >= @price
begin
with base(seq, cust_id, cash, create_dt, charge_type, calc_rank)
as
(
select
b.seq
, b.cust_id
, b.cash
, b.create_dt
, a.charge_type
, row_number() over(order by a.calc_rank, b.create_dt) calc_rank
from bill_policy a inner join bill b
on a.charge_type = b.charge_type
),
cte(seq, cust_id, cash, remain_cash, deduction_cash, create_dt, charge_type, calc_rank, level)
as
(
select --Anchor member
seq
, cust_id
, cash
, case when cash - @price <= 0 then 0 else cash - @price end remain_cash
, cash - @price deduction_cash
, create_dt
, charge_type
, calc_rank
, 1 as level
from base
where calc_rank = 1
union all
select --Recusive member
a.seq
, a.cust_id
, a.cash
, case when a.cash + b.deduction_cash <= 0 then 0 else a.cash + b.deduction_cash end remain_cash
, a.cash + b.deduction_cash deduction_cash
, a.create_dt
, a.charge_type
, a.calc_rank
, b.level + 1
from base a inner join cte b
on a.calc_rank = b.level + 1
where a.cash + b.deduction_cash < 0 --where 절은 어디까지 update 해야 하는지 구분하는 부분
or a.cash > b.deduction_cash --where 절을 빼면 마지막 순서의 Row는 남은 캐시 합계가 된다.
)
/*
update a
set a.cash = b.remain_cash
from bill a inner join cte b
on a.seq = b.seq;
*/
/*
select
seq 차감일련번호
, cust_id 고객ID
, cash 캐시
, remain_cash 남은캐시
, deduction_cash 차감해야할캐시
, charge_type 요금유형
, calc_rank 차감우선순위
, create_dt 캐시충전일시
from cte;
or
*/
--로그로 남기는 것도 한 번에..
--2005에서 되던가? 아.. 귀찮아.. 2008에서는 되니까.. 2005는 테스트 해보시길..
update a
set a.cash = b.remain_cash
output
deleted.seq
, deleted.cust_id
, deleted.cash
, b.remain_cash
, b.deduction_cash
, deleted.charge_type
, b.calc_rank
, deleted.create_dt
into @billlog
from bill a inner join cte b
on a.seq = b.seq;
select
seq 차감일련번호
, cust_id 고객ID
, cash 캐시
, remain_cash 남은캐시
, deduction_cash 앞으로차감해야할캐시
, @price + case when deduction_cash < 0 then deduction_cash else 0 end 차감된캐시
, charge_type 요금유형
, calc_rank 차감우선순위
, create_dt 캐시충전일시
from @billlog
order by calc_rank
end else
select '캐시가부족하니까충전해유~';
go
--select * from bill;
![[-]](/moniwiki/imgs/plugin/arrup.png "[-]")
![[+]](/moniwiki/imgs/plugin/arrdown.png "[+]")
