急，高手请帮忙！存储过程开发问题
刚学习SQL开发没多久，基本概念学的差不多了，现在要写个存储过程，主要要求如下：
有两张表用来计算收费，比如‘业务1’分三种收费各安一定比例（如表1），然后三种收费又安一定比例分配（如表2），其中‘pid’对应的就是表1中id，也就是表1中的收费1又按三种收费来分配。 如何使用存储过程来输出一个结果集（入参包括业务名和收费金额）， 重要的是如果有固定收费的话要先减去固定的然后才能算其他比例的。 如要查业务1，1000的结果集，输出如表3。
id业务 nnamentypenvalue
1业务1 收费1比例30
2业务1 收费2比例70
3业务1 收费3固定10
表1
idpidnnamentypenvalue
11费1－1固定5
21费1－2比例50.0000
31费1－3比例50.0000
42费2比例100.0000
53费3比例100.0000
表2
id业务nname1nname1nvalue
1业务1收费1费1－15
2业务1收费1费1－2((1000-10)*30%-5)*50%
3业务1收费1费1－3((1000-10)*30%-5)*50%
4业务1收费2费2(1000-10)*70%
5业务1收费3费310
表3
急～～～在线等，请高手提示下该怎么做？万分感谢。
[解决办法]
--建立环境
create table 表1 (
idint,
业务 nvarchar(20),
nnamenvarchar(20),
ntypenvarchar(20),
nvalue numeric(10,2)
)
create table 表2 (
idint,
pidint,
nnamenvarchar(20),
ntypenvarchar(20),
nvalue numeric(10,2)
)
insert 表1 select
1,N '业务1 ', N '收费1 ',N '比例 ',30
union all select
2,N '业务1 ', N '收费2 ',N '比例 ',70
union all select
3,N '业务1 ', N '收费3 ',N '固定 ',10
insert 表2 select
1,1,N '费1－1 ',N '固定 ',5
union all select
2,1,N '费1－2 ',N '比例 ',50.0000
union all select
3,1,N '费1－3 ',N '比例 ',50.0000
union all select
4,2,N '费2 ',N '比例 ',100.0000
union all select
5,3,N '费3 ',N '比例 ',100.0000
go
--存储过程
create proc p_cal
@业务 nvarchar(20),
@收费金额 numeric(10,2)
as
--存放结果
declare @表3 table(
idint IDENTITY(1,1),
pidint ,
业务 nvarchar(20),
nname1nvarchar(20),
nname2nvarchar(20),
nvalue numeric(10,2)
)
--存放中间数据
declare @t table(
pidint,
业务 nvarchar(20),
nname1nvarchar(20),
nvalue numeric(10,2)
)
insert @t
select id,业务,nname,nvalue
from 表1
where 业务=@业务
and ntype=N '固定 '
set @收费金额=@收费金额-(select sum(nvalue) from @t)
insert @t
select id,业务,nname,nvalue=@收费金额*nvalue/100
from 表1
where 业务=@业务
and ntype=N '比例 '
insert @表3(pid,业务,nname1,nname2,nvalue)
select a.pid,t.业务,t.nname1,a.nname,a.nvalue
from 表2 a,@t t
where a.pid=t.pid
and a.ntype=N '固定 '
insert @表3(pid,业务,nname1,nname2,nvalue)
select a.pid,t.业务,t.nname1,a.nname,nvalue=(t.nvalue-isnull((select sum(nvalue) from @表3 where pid=a.pid),0))*a.nvalue/100
from 表2 a,@t t
where a.pid=t.pid
and a.ntype=N '比例 '
--显示结果
select id,业务,nname1,nname2,nvalue from @表3
order by 业务,nname1,nname2,id
go
--调用测试
p_cal N '业务1 ',1000
--结果
id 业务 nname1 nname2 nvalue
----------- -------------------- -------------------- -------------------- ------------
1 业务1 收费1 费1－1 5.00
2 业务1 收费1 费1－2 146.00
3 业务1 收费1 费1－3 146.00
4 业务1 收费2 费2 693.00
5 业务1 收费3 费3 10.00
（所影响的行数为 5 行）
[解决办法]
drop table 表1,表2
go
create table 表1(id int,业务 varchar(10),nname varchar(10),type varchar(10),nvalue numeric(20,6))
insert into 表1
select 1, '业务1 ', '收费1 ', '比例 ',30
union all select 2, '业务1 ', '收费2 ', '比例 ',70
union all select 3, '业务1 ', '收费3 ', '固定 ',10
create table 表2(id int,pid int,nname varchar(20),ntype varchar(10),nvalue numeric(20,6))
insert into 表2
select 1,1, '费1－1 ', '固定 ',5
union all select 2,1, '费1－2 ', '比例 ',50.0000
union all select 3,1, '费1－3 ', '比例 ',50.0000
union all select 4,2, '费2 ', '比例 ',100.0000
union all select 5,3, '费3 ', '比例 ',100.0000
create proc up_test1(@type varchar(10),@nvalue numeric(20,6))
as
select b.id,
a.业务,
a.nname,
b.nname as b_nname,
case when b.ntype= '固定 ' then b.nvalue
when a.type= '固定 ' then a.nvalue
else ((@nvalue-(select isnull(sum(aa.nvalue),0) from 表1 aa where aa.type= '固定 ' and aa.业务=@type))*
(select isnull(sum(bb.nvalue),0)/100 from 表1 bb where bb.type= '比例 ' and bb.nname=a.nname)-
(select isnull(sum(cc.nvalue),0) from 表2 cc where cc.ntype= '固定 ' and cc.id=b.pid))*b.nvalue/100 end as nvalue
from 表2 b
left join 表1 a on b.pid=a.id
go
exec up_test1 '业务1 ',1000
/*
id 业务 nname b_nname nvalue
----------- ---------- ---------- -------------------- ----------------------------------------
1 业务1 收费1 费1－1 5.000000
2 业务1 收费1 费1－2 146.000000
3 业务1 收费1 费1－3 146.000000
4 业务1 收费2 费2 693.000000
5 业务1 收费3 费3 10.000000
（所影响的行数为 5 行）
*/
[解决办法]
Create proc Test
@ntype varchar(10),
@nvalue decimal(10,2)
as
declare @a table(id int, 业务 varchar(10), nname varchar(10), ntype varchar(10), nvalue int)
insert @a select 1, '业务1 ', '收费1 ', '比例 ', 30
union all select 2 , '业务1 ', '收费2 ', '比例 ', 70
union all select 3 , '业务1 ', '收费3 ', '固定 ', 10
declare @b table(id int, pid int, nname varchar(10), ntype varchar(10), nvalue int)
insert @b select 1, 1 , '费1－1 ', '固定 ', 5
union all select 2 ,1 , '费1－2 ', '比例 ', 50.0000
union all select 3 ,1 , '费1－3 ', '比例 ', 50.0000
union all select 4 ,2 , '费2 ', '比例 ', 100.0000
union all select 5 ,3 , '费3 ', '比例 ', 100.0000
select 业务,a.nname nname1,a.ntype ntype1 ,a.nvalue nvalue1,
b.id,b.nname nname2,b.ntype ntype2,b.nvalue nvalue2,
value1=(case when a.ntype= '固定 ' then a.nvalue else (@nvalue-isnull((select sum(nvalue) from @a where 业务=a.业务 and ntype= '固定 '),0))*(a.nvalue*1.0/100) end) into #tmp from @a a Left Join @b b on a.id=b.pid where 业务=@ntype
select id, 业务,nname1,nname2,value2=(case when ntype2= '固定 ' then nvalue2
when ntype1= '固定 ' then value1
else (value1-isnull((select sum(nvalue2) from #tmp where nname1=a.nname1 and ntype2= '固定 '),0))*(nvalue2*1.0/100) end) from #tmp a
drop table #tmp
test '业务1 ',1000
--result
/*
id 业务 nname1 nname2 value2
----------- ---------- ---------- ---------- -----------------------------------
1 业务1 收费1 费1－1 5.000000
2 业务1 收费1 费1－2 146.000000
3 业务1 收费1 费1－3 146.000000
4 业务1 收费2 费2 693.000000
5 业务1 收费3 费3 10.000000
（所影响的行数为 5 行）
*/
[解决办法]
create table T1(id int,yewu varchar(10),nname varchar(10),ntype varchar(10),nvalue int)
insert into T1
select 1, '1 ', '收1 ', '比例 ',30 union all
select 2, '1 ', '收2 ', '比例 ',70 union all
select 3, '1 ', '收3 ', '固定 ',10
create table T2(id int,pid int,nname varchar(10),ntype varchar(10),nvalue int)
insert into T2
select 1,1, '1-1 ', '固定 ',5 union all
select 2,1, '1-2 ', '比例 ',50 union all
select 3,1, '1-3 ', '比例 ',50 union all
select 4,2, '2 ', '比例 ',100 union all
select 5,3, '3 ', '比例 ',100
GO
CReate proc usp_test(@yewu varchar(10),@nmoney int)
AS
select T2.id,@yewu as yewu,T.nname,T2.nname,
case when T2.ntype= '固定 ' then T2.nvalue
else (T.nvalue-isnull((select sum(a.nvalue) from T2 a where yewu=@yewu and pid=T2.pid and ntype= '固定 '),0))*0.01*T2.nvalue
end as nvalue
from T2
inner join
(select id,yewu,nname,
case when ntype= '固定 ' then nvalue
else (@nmoney-isnull((select sum(nvalue) from T1 where yewu=@yewu and ntype= '固定 ' ),0))*nvalue*0.01
end as nvalue
from T1
where yewu=@yewu) T
on T2.pid=T.id
Go
exec usp_test '1 ',1000
/*
id yewu nname nname nvalue
----------- ---------- ---------- ---------- ------------------------------
1 1 收1 1-1 5.0000
2 1 收1 1-2 146.0000
3 1 收1 1-3 146.0000
4 1 收2 2 693.0000
5 1 收3 3 10.0000
*/
drop table t1,t2
drop proc usp_test