22,210
社区成员
发帖
与我相关
我的任务
分享
create table t_order (orderid int,summoney numeric(12,2) primary key(orderid))
create table t_orderitem(orderid int,wareid int,quantity int primary key(orderid,wareid))
create table t_ware (wareid int,warename varchar(20),price numeric(8,2) primary key(wareid))
insert t_ware select 1,'铅笔',0
union all select 2,'餐桌',0
union all select 3,'彩电',0
union all select 4,'蛋糕',0
insert t_order select 1,3351
union all select 2,2185
union all select 3,2334
union all select 4,7533
insert t_orderitem select 1,1,1
union all select 1,2,3
union all select 1,3,3
union all select 1,4,5
union all select 2,1,5
union all select 2,2,1
union all select 2,3,2
union all select 2,4,8
union all select 3,1,4
union all select 3,2,3
union all select 3,3,2
union all select 3,4,3
union all select 4,1,3
union all select 4,2,5
union all select 4,3,7
union all select 4,4,3
go
create proc xyz
as
begin
--定义方程式表及变量
declare @ii int,@i int,@j int
declare @ic varchar(10),@jc varchar(10)
declare @sql varchar(1000),@sql1 varchar(1000)
declare @price numeric(8,2)
create table #pp (p numeric(8,2))
--计算变量数
select @ii = count(*) from t_order
if @ii < 2
begin
select '没有足够的数据,不能计算'
return
end
--生成方程式
create table #tt(id int,s numeric(20,2))
set @i = 1
set @sql = 'select a.orderid,a.summoney'
while @i <= @ii
begin
set @ic = rtrim(@i)
set @sql = @sql +',0,sum(case wareid when '+@ic+' then quantity else 0 end)'
exec('alter table #tt add a'+@ic+' bigint,b'+@ic+' bigint')
set @i = @i +1
end
set @sql = @sql +' from t_order a,t_orderitem b where a.orderid = b.orderid group by a.orderid,a.summoney'
insert #tt exec(@sql)
--消除
set @i = 1
while @i < @ii
begin
set @ic = rtrim(@i)
set @sql = 'update #tt set s = a.s*b.b'+@ic+'-a.b'+@ic+'*b.s,b'+@ic+'=0'
set @sql1 = ' from #tt a,(select s,b'+@ic
set @j = @i+1
while @j <=@ii
begin
set @jc = rtrim(@j)
set @sql = @sql + ',b'+@jc+'=a.b'+@jc+'*b.b'+@ic+'-a.b'+@ic+'*b.b'+@jc
set @sql1 = @sql1+',b'+@jc
set @j = @j + 1
end
set @sql = @sql + @sql1 + ' from #tt where id = '+@ic+' ) b where a.id > '+@ic
set @i = @i + 1
exec(@sql)
end
--计算
set @i = @ii
while @i >0
begin
set @ic = rtrim(@i)
set @sql = 'select (s '
set @j = @i+1
while @j <=@ii
begin
set @jc = rtrim(@j)
set @sql = @sql + '-a'+@jc+'*b'+@jc
set @j = @j + 1
end
set @sql = @sql + ')/b'+@ic+' from #tt where id = '+@ic
delete #pp
insert #pp exec(@sql)
select @price = p from #pp
set @sql ='update #tt set a'+@ic+'='+rtrim(@price)
exec(@sql)
set @sql ='update t_ware set price = '+rtrim(@price) +' where wareid ='+@ic
exec(@sql)
set @i = @i - 1
end
select * from t_ware
end
go
xyz
drop table t_order
drop table t_orderitem
drop table t_ware
drop proc xyz
create table t_order (orderid int,summoney numeric(12,2) primary key(orderid))
create table t_orderitem(orderid int,wareid int,quantity int primary key(orderid,wareid))
create table t_ware (wareid int,warename varchar(20),price numeric(8,2) primary key(wareid))
insert t_ware select 1,'铅笔',0
union all select 2,'餐桌',0
union all select 3,'彩电',0
union all select 4,'蛋糕',0
insert t_order select 1,3351
union all select 2,2185
union all select 3,2334
union all select 4,7533
insert t_orderitem select 1,1,1
union all select 1,2,3
union all select 1,3,3
union all select 1,4,5
union all select 2,1,5
union all select 2,2,1
union all select 2,3,2
union all select 2,4,8
union all select 3,1,4
union all select 3,2,3
union all select 3,3,2
union all select 3,4,3
union all select 4,1,3
union all select 4,2,5
union all select 4,3,7
union all select 4,4,3
go
create function dbo.getq(@i int,@j int)
returns numeric(8,2)
as
begin
declare @s int
set @s = 0
if @j > 0 select @s = quantity from t_orderitem where orderid = @i and wareid = @j
if @j =0 select @s = summoney from t_order where orderid = @i
if @j < 0 select @s = price from t_ware where wareid = @i
return @s
end
go
--定义变量
declare @ii int,@i int
select @ii = count(*) from t_order
--消除
set @i = 1
while @i < @ii
begin
update t_orderitem set quantity = quantity*dbo.getq(@i,@i) - dbo.getq(@i,wareid)*dbo.getq(orderid,@i)
where orderid > @i and wareid > @i
update t_order set summoney = summoney*dbo.getq(@i,@i) - dbo.getq(@i,0)*dbo.getq(orderid,@i) where orderid > @i
update t_orderitem set quantity = 0 where orderid > @i and wareid = @i
set @i = @i + 1
end
--计算
set @i = @ii
while @i > 0
begin
update t_ware set price = summoney / quantity
from t_orderitem a,t_order b,t_ware c where a.orderid = b.orderid and a.orderid = @i and a.wareid = @i and c.wareid = @i
update t_order set summoney = summoney - quantity * dbo.getq(@i,0-@i)
from t_orderitem a,t_order b where a.orderid = b.orderid and a.wareid = @i
set @i = @i -1
end
--结果
select * from t_ware
--清除
drop table t_order
drop table t_orderitem
drop table t_ware
drop function dbo.getq