原帖地址:
http://community.csdn.net/expert/topic/3485/3485588.xml?temp=.8813745
--示例數據
create table sale(date datetime,code varchar(10),amt int)
insert sale select '2004-10-22','aa',15000
union all select '2004-10-22','bb',18000
union all select '2004-10-22','cc',20000
union all select '2004-10-23','aa',21000
union all select '2004-10-23','bb',18500
union all select '2004-10-23','cc',19600
create table dept(code varchar(10),name varchar(10))
insert dept select 'aa','中餐廳'
union all select 'bb','西餐廳'
union all select 'cc','客房部'
union all select 'dd','ktv部'
create table cost(date datetime,code varchar(10),amt int)
insert cost select '2004-10-22','aa',5000
union all select '2004-10-22','bb',7000
union all select '2004-10-22','cc',11000
union all select '2004-10-23','aa',12000
union all select '2004-10-23','bb',8500
union all select '2004-10-23','cc',9600
create table means(code varchar(10),seq int,[desc] varchar(10),formula nvarchar(4000))
insert means select 'aa',1,'銷售收入','^[email protected]^'
union all select 'aa',2,'銷售成本','^[email protected]^'
union all select 'aa',3,'上交利潤','([1] - [2])*0.3'
union all select 'aa',4,'凈利潤' ,'[1]-[2]-[3]'
go
/*--問題處理要求描述
寫一個計算的存儲過程,完成根據各基礎資料及計算方法,將數據放到利潤明細表中!
如用戶輸入的查詢條件是部門代號aa(@code),日期為:2004-10-22(@date)
則處理過程如下,
1:從利潤計算方法表(means)讀取code = aa到臨時表
select * into #temp from means where code = @code order by seq
2:用游標循環處理每一個項目,按seq從小到大的順序,如第一個項目
insert into gain_detail(code,date,desc,amt)
select @code,@date,@desc,sale.amt from sale where code = @code and date = @date
其它項目類似
公式說明:
1.^表名@字段名^ : 例如: ^[email protected]^ 表示從 sale 表取 amt 字段的值,取值條件是 [email protected] and [email protected]
2.[seq] : 例如: [1]-[2]-[3],[]之間為引用本部門前面的計算結果項,[1]表示是本部門的銷售收入[2]表示銷售成本,其它類似
3.其他的是標準的計算表達式
--*/
--問題處理:
--公式計算的存儲過程
create proc p_calc
@formula nvarchar(4000), --要計算的公式
@code varchar(10), --部門代碼
@date datetime, --計算的日期
@amt int out --計算的結果
as
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@i int,@j int
--外部計算
set @i=patindex('%^%@%^%',@formula)
while @i>0
begin
select @j=charindex('@',@formula,@i)
,@s1=substring(@formula,@i,@[email protected])
,@s2=' from '
+substring(@formula,@i+1,@[email protected])
+' where [email protected] and [email protected]'
,@i=charindex('^',@formula,@j)
,@[email protected]+substring(@formula,@j,@[email protected]+1)
,@s2='select @amt='
+substring(@formula,@j+1,@[email protected])
[email protected]
exec sp_executesql @s2
,n'@code varchar(10),@date datetime,@amt int out'
,@code,@date,@amt out
select @formula=replace(@formula,@s1,@amt)
,@i=patindex('%^%@%^%',@formula)
end
--計算內部公式
select @i=patindex('%[[]%]%',@formula)
while @i>0
begin
select @j=charindex(']',@formula,@i)
,@s1=substring(@formula,@i,@[email protected]+1)
,@s2='select @amt=amt from #t where seq='
+substring(@formula,@i+1,@[email protected])
exec sp_executesql @s2,n'@amt int out',@amt out
select @formula=replace(@formula,@s1,@amt)
,@i=patindex('%[[]%]%',@formula)
end
--計算最終結果
set @s2='select @amt='[email protected]
exec sp_executesql @s2,n'@amt int out',@amt out
go
--計算利潤明細資料的
create proc p_qry
@code varchar(10), --要計算的部門代碼
@date datetime --計算的日期
as
set nocount on
create table #t(code varchar(10),date varchar(10),[desc] varchar(10),amt int,seq int)
declare @dt varchar(10),@desc varchar(10),@amt int,@formula nvarchar(4000),@seq int
set @dt=convert(char(10),@date,120)
declare tb cursor local for
select [desc],formula,seq from means
where [email protected] order by seq
open tb
fetch tb into @desc,@formula,@seq
while @@fetch_status=0
begin
--計算公式
exec p_calc @formula,@code,@date,@amt out
insert #t values(@code,@dt,@desc,@amt,@seq)
fetch tb into @desc,@formula,@seq
end
close tb
deallocate tb
select 部門代號=code,日期=date,項目內容=[desc],金額=amt from #t
go
--調用
exec p_qry 'aa','2004-10-22'
go
--刪除測試
drop table sale,dept,cost,means
drop proc p_qry,p_calc
go
/*--測試結果
部門代號 日期 項目內容 金額
---------- ---------- ---------- -----------
aa 2004-10-22 銷售收入 15000
aa 2004-10-22 銷售成本 5000
aa 2004-10-22 上交利潤 3000
aa 2004-10-22 凈利潤 7000
--*/
