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;
