按先进先出销售数据,这个SQL可以的到冲减库存后的数据, 但是怎么改 可以得到已经减掉了的库存数据呢,得到如下?
编码 库存
001 6
001 19
001 0
002 15
002 3
declare @t table(code varchar(10), id int, num int)
insert into @t select '001',1,6
insert into @t select '001',2,20
insert into @t select '001',3,10
insert into @t select '002',1,15
insert into @t select '002',2,8
--销售表 code 是 商品编码 num 是 商品数量
declare @xs table(code varchar(10), num int)
insert into @xs select '001', 25
insert into @xs select '002', 18;with test1 as(
select t.code,t.num,total=(select sum(num) from @t where code=t.code and id<=t.id)-xs.num from @t t inner join @xs xs on t.code=xs.code
)
select code,total=case when total<=0 then 0 when total<num then total else num end from test1
;with tb as (
select t.code,t.num,t.id,
x.num-(select SUM(num) from @t where code=t.code and id<=t.id) sy,
x.num xnum
from @t t inner join @xs x on t.code=x.code
)
select code,
(case when sy>=0 then num
else isnull((select top 1 (case when sy>0 then sy else 0 end)
from tb where t.code=code and id<t.id order by id desc),xnum) end) cc
from tb t;
;with test1 as(
select t.id,t.code,t.num,total=(select sum(num) from @t where code=t.code and id<=t.id)-xs.num from @t t inner join @xs xs on t.code=xs.code order by id desc
)
select code,total=case when total<=0 then 0 when total<num then total else num end from test1如果是后进先出 order by id desc 就会出错,怎么改消息 1033,级别 15,状态 1,第 16 行
除非另外还指定了 TOP 或 FOR XML,否则,ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
子查询中不可使用ORDER BY
你CTE在外层查询指定;with test1 as(
select t.id,t.code,t.num,total=(select sum(num) from @t where code=t.code and id<=t.id)-xs.num from @t t inner join @xs xs on t.code=xs.code order by id desc
)
select code,total=case when total<=0 then 0 when total<num then total else num end from test1 ORDER BY id DESC