Contents

1
2 MSSQL 2005 蟲覦覯
3 蟆磯
4 讌 磯ジ 豢


1 #

れ螻 螳 企 .
image001.jpg

螻螳 yasicom bill 企螻 螳 貂螳 豢 . 蠏碁Μ螻 bill_policy企 企 蠍覿 谿螳 蟆語 螻一 一螳 襷り襖 . 豢 蠍 螳朱 襾殊 豢 貂螳 谿螳. 讀, 豢企. 蠏碁覩襦 れ螻 螳 一襯 螳.

image002.jpg

襷 17,000讌襴 危 蟲襷ろる, れ螻 螳 谿螳伎 .

image003.jpg

  1. 1覯讌 Row: 500 - 17000 = -16500
  2. 2覯讌 Row: 2000 - 16500 = -14500
  3. 3覯讌 Row: 500 - 14500 = -14000
  4. 4覯讌 Row: 10000 - 14000 = -4000
  5. 5覯讌 Row: 5000 - 4000 = 1000

讀, 17,000 讌襴 危 蟲襷ろ朱 豢 貂螳 れ螻 螳 谿螳(一危)伎 .

image004.jpg

2 MSSQL 2005 蟲覦覯 #


2000覯 MSSQL Server 企 蟲 る 襷れ 一誤. 襷 覦覯 螳企慨讌襷 貉れ襷 襭 朱蟾 襷企. 伎 2005 覯 CTE襯 蠏貎朱Μ襯 伎 豢 覦 谿螳 Row 蠏殊 企 螳ロ伎. 覘 讌朱 螳ロ伎り 蟆朱 讌企 手 襷蠍磯 譟郁 覘m. 伎蟇 れ螻 螳 蟲 覲伎.

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

image004.jpg

select 
	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

image006.jpg

--襷 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;


image007.jpg

谿螳襦蠏
image008.jpg

3 蟆磯 #

DBMS 螳 蠍磯レ 伎覃 襷 企蟆 讌ъ 貊襦 谿螳 蟇一 覈 襦讌 . CTE襯 谿 螻一 螻, output 伎 襦蠏碁ゼ 蠍磯 蟆 讌 覲伎. 覃 螳覦螳 襷 譴企 蟆朱 覲伎碁. CTE 朱 覿螳 譬 朱Μ り . 讌讌 蠏碁郁? 1螳 蟇磯襯 豌襴蠍 伎 覦 觜 覈 一碁慨. 襷 觜訣 蟆企 螳. 襷 磯り る CTE 朱 誤 覿襷 一 譟郁 觜給控蟆 ′朱 . (讀 螳蟆 語語 伎 觜曙 覦 伎 蟆 譬 襭 .)


4 讌 磯ジ 豢 #

declare 
	@deduct int
,	@id char(12)

set @id = 'yasicom'
set @deduct = 41000

;with base
as
(
	select 
		a.seq
	,	a.id
	,	a.point
	,	row_number() over(partition by a.cn order by a.seq) calc_rank
	from stg.sales.Members_UserPoint_Log_T a
		left join stg.sales.Members_UserPoint_MoneyLog_T b
			on a.seq = b.seq
	where a.id = @id
	and a.seq >= 86
	and a.point >= 0
), cte
as
(
	select
		seq
	,	id
	,	point
	,	case when point - @deduct <= 0 then 0 else point - @deduct end residual_point
	,	case when point >= @deduct then @deduct else point - @deduct end residual_deduct_point
	,	case when point >= @deduct then @deduct - point else point - @deduct end deducted_point
	,	calc_rank
	,	1 level
	,	point acc_point
	from base
	where calc_rank = 1
	union all
	select --Recusive member
		a.seq
	,	a.id
	,	a.point
	,	case when a.point + b.residual_deduct_point <= 0 then 0 else a.point + b.residual_deduct_point end residual_point
	,	case when a.point + b.residual_deduct_point <= 0 then a.point + b.residual_deduct_point else 0 end residual_deduct_point
	,	a.point + b.residual_deduct_point deducted_point
	,	a.calc_rank
	,	b.level + 1
	,	a.point + b.acc_point acc_point
	from base a inner join cte b
	on a.calc_rank = b.level + 1
	and a.id = b.id
	--where a.point + b.deduct_point < 0 --where  企蟾讌 update 伎 讌 蟲覿 覿覿
	--or a.point > b.deduct_point
)	
select 
	seq
,	id
,	@deduct total_deduct_point --谿螳伎  豐 誤
,	point -- 讌蠍覦 誤
,	residual_point --谿螳  誤
,	acc_point --讌蠍覦 誤
from cte
where point <> residual_point
from cte;
蟆郁骸
02_01.jpg