22,301
社区成员




Procedure executed successfully
时间: 6.597s
ALTER procedure dbo.P_gys_book
@xn varchar(9),@xq int
as
begin
set nocount on
--declare @xn varchar(9) = '2019-2020',@xq int = 1;
merge into dbo.gys_book a
using (select @xn xn,@xq xq,a.gys,A.isbn,a.jcmc,a.jczz,a.cbs,a.listprice,a.zk,a.CK_zsje,a.CK_total,a.CK_qty,b.RK_zsje,b.RK_total,b.RK_num rk_qty
from
(select a.xn,a.xq,c.isbn,c.jcmc,c.jczz,c.cbs,c.gys,d.listprice ,d.zk,sum(b.qty) as CK_qty,round(sum(d.listprice*d.zk*b.qty),2) CK_zsje,sum(d.listprice*b.qty) CK_total
from dbo.OrderHeader a INNER JOIN dbo.OrderDetail b ON a.id = b.orderid
INNER JOIN dbo.Jcjbxxb c on b.bookid = c.id
inner join dbo.Jcjbxxb_ListPriceHistory d on c.id = d.bookid and a.xn = d.xn and a.xq =d.xq
where a.xn = @xn and a.xq= @xq
group by a.xn,a.xq,c.isbn,c.jcmc,c.jczz,c.cbs,c.gys,d.listprice,d.zk
) a
left join
(select xn,xq,isbn,gys,sum(num) as RK_num,isnull(sum(total),0) as RK_total,sum(zsje) as RK_zsje from guest.jcrkb where xn = @xn and xq = @xq GROUP BY xn,xq,isbn,gys
) as b ON a.xn = b.xn and a.xq = b.xq and a.isbn = b.isbn and a.gys = b.gys) b on (a.xn = b.xn and a.xq = b.xq and a.gys = b.gys and a.isbn = b.isbn )
when matched then
update
set a.ck_zsje = b.ck_zsje,a.ck_total = b.ck_total,a.ck_qty = b.ck_qty,a.rk_zsje = b.rk_zsje,a.rk_total = b.rk_total,a.rk_qty = b.rk_qty,a.price = b.listprice,a.cbs = b.cbs ,a.jcmc = b.jcmc,a.jczz = b.jczz
when not matched then
insert (xn,xq,gys,isbn,jcmc,jczz,cbs,price,zk,ck_zsje,ck_total,ck_qty,rk_zsje,rk_total,rk_qty)
values(b.xn,b.xq,b.gys,b.isbn,b.jcmc,b.jczz,b.cbs,b.listprice,b.zk,b.ck_zsje,b.ck_total,b.ck_qty,isnull(b.rk_zsje,0),isnull(b.rk_total,0),isnull(b.rk_qty,0));
--统计影响的行数
select @@rowcount;
end;
把参数赋值给另外新建的值就可以了