有些错误,修正如下:
insert t_order select 1,3351
union all select 2,2185
union all select 3,2334
union all select 4,7533
insert t_order select 1,3351
union all select 2,2185
union all select 3,2334
union all select 4,7533
1 1.00
2 100.00
3 1000.00
4 10.00
你这结果怎么算出来的?
订单1: 1 * A1 + 3 * A2 + 3 * A3 + 5 * A4 = 3351
订单2: 5 * A1 + 1 * A2 + 2 * A3 + 8 * A4 = 2185
订单3: 4 * A1 + 3 * A2 + 2 * A3 + 3 * A4 = 2334
订单4: 3 * A1 + 5 * A2 + 7 * A3 + 3 * A4 = 7533不能算出A1,A2,A3,A4吗??????
a+3b+3c+5d=3351
5a+b+2c+8d=2185
4a+3b+2c+3d=2334
3+5b+7c+3d=7533
1 * A1 + 3 * A2 + 3 * A3 + 5 * A4 = 3351
5 * A1 + 1 * A2 + 2 * A3 + 8 * A4 = 2185
4 * A1 + 3 * A2 + 2 * A3 + 3 * A4 = 2334
3 * A1 + 5 * A2 + 7 * A3 + 3 * A4 = 7533 1 * A1 + 3 * A2 + 3 * A3 + 5 * A4 = 3351 (* 5)
5A1 + 15A2 + 15A3 + 25A4 = 3351*5 (-)
5 * A1 + 1 * A2 + 2 * A3 + 8 * A4 = 2185
14A2 + 13A3 + 17A4 = 14570
1 * A1 + 3 * A2 + 3 * A3 + 5 * A4 = 3351 (*4)
4A1 + 12A2 + 12A3 + 20A4 = 13404 (-)
4 * A1 + 3 * A2 + 2 * A3 + 3 * A4 = 2334
9A2 + 10A3 + 17A4 = 11070 1 * A1 + 3 * A2 + 3 * A3 + 5 * A4 = 3351 (*3)
3A1 + 9A2 + 9A3 + 15A4 = 10053 (-)
3 * A1 + 5 * A2 + 7 * A3 + 3 * A4 = 7533
4A2 + 2A3 + 12A4 = 2520--------------------------------------------------
消去A1了。
14A2 + 13A3 + 17A4 = 14570
9A2 + 10A3 + 17A4 = 11070
4A2 + 2A3 + 12A4 = 252014A2 + 13A3 + 17A4 = 14570 (-)
9A2 + 10A3 + 17A4 = 11070
5A2 + 3A3 = 35009A2 + 10A3 + 17A4 = 11070 (*12)
108A2 + 120A3 + 204A4 = 132840
4A2 + 2A3 + 12A4 = 2520 (*17)
68A2 + 34A3 + 204A4 = 43840
然后相减
40A2 + 86A3 = 9000-------------------------------------
--消去A4
5A2 + 3A3 = 3500
40A2 + 86A3 = 900005A2 + 3A3 = 3500 (* 8)
40A2 + 24A3 = 2800
--通过这两求A3
40A2 + 86A3 = 90000
40A2 + 24A3 = 28000A3 = 1000-------------------------
把A3代入:40A2 + 24A3 = 28000
A2 = 100-----------------
把A2 = 100,A3 = 1000代入:68A2 + 34A3 + 204A4 = 43840
A4 = 10---------------------
把A2 = 100,A3 = 1000,A4 = 10代入:1 * A1 + 3 * A2 + 3 * A3 + 5 * A4 = 3351
A1 = 1
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
结果:
1 铅笔 1.00
2 餐桌 100.00
3 彩电 1000.00
4 蛋糕 10.00
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
gocreate 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这个更简单
结果
1 铅笔 1.00
2 餐桌 100.00
3 彩电 1000.00
4 蛋糕 10.00