表格式如下,同一个型号的产品可能有多条记录名称 数量 入库日期
产品A 200 2014-05-07
产品A 300 2014-03-05
产品B 200 2014-03-05现在要仓库要出库存,把产品A 按入库的先后减掉400个 ,先入库的先减掉.
也就是表 会变成这样名称 数量 入库日期
产品A 100 2014-05-07
产品B 200 2014-03-05请问这种情况 可以用SQL语句来写吗?
编程来逐行计算的话我要可以做到的.
产品A 200 2014-05-07
产品A 300 2014-03-05
产品B 200 2014-03-05现在要仓库要出库存,把产品A 按入库的先后减掉400个 ,先入库的先减掉.
也就是表 会变成这样名称 数量 入库日期
产品A 100 2014-05-07
产品B 200 2014-03-05请问这种情况 可以用SQL语句来写吗?
编程来逐行计算的话我要可以做到的.
create table tb(名称 varchar(10) , 数量 int, 入库日期 datetime)
insert into tb
select '产品A','200','2014-05-07' union all
select '产品A','300','2014-03-05' union all
select '产品B','200','2014-03-05'
go
select 名称,sum(数量)-400,max(入库日期) from tb where 名称='产品A' group by 名称
-- 名称 (无列名) (无列名)
--产品A 100 2014-05-07 00:00:00.000
if not object_ID('Tempdb..#1') is null
drop table #1
Go
Create table #1([名称] nvarchar(3),[数量] int,[入库日期] Datetime)
Insert #1
select N'产品A',200,'2014-05-07' union all
select N'产品A',300,'2014-03-05' union all
select N'产品B',200,'2014-03-05'
Godeclare @Qty int =400
declare @Name nvarchar(50)=N'产品A'Select a.[名称],b.[数量]-@Qty as [数量],a.[入库日期]
from #1 a
cross apply(select sum(x.[数量]) as [数量] from #1 x where x.[名称]=a.[名称] and x.[入库日期]<=a.[入库日期]) b
where a.[名称]=@Name
and b.[数量]-@Qty>0
union all
Select a.[名称],a.[数量],a.[入库日期]
from #1 a
where a.[名称]<>N'产品A'
if not object_ID('Tempdb..#1') is null
drop table #1
Go
Create table #1([名称] nvarchar(3),[数量] int,[入库日期] Datetime)
Insert #1
select N'产品A',200,'2014-05-07' union all
select N'产品A',300,'2014-03-05' union all
select N'产品B',200,'2014-03-05'
Godeclare @Qty int =400
declare @Name nvarchar(50)=N'产品A'Select a.[名称],b.[数量]-@Qty as [数量],a.[入库日期]
from #1 a
cross apply(select sum(x.[数量]) as [数量] from #1 x where x.[名称]=a.[名称] and x.[入库日期]<=a.[入库日期]) b
where a.[名称]=@Name
and b.[数量]-@Qty>0
union all
Select a.[名称],a.[数量],a.[入库日期]
from #1 a
where a.[名称]<>@Name
(
SELECT N'产品A', 200, '2014-05-07' UNION ALL
SELECT N'产品A', 100, '2014-03-06' UNION ALL
SELECT N'产品A', 300, '2014-03-05' UNION ALL
SELECT N'产品B', 200, '2014-03-05'
)
,test1 AS (
SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) ID
,ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY RcveDate) ID1
,NAME
,CASE WHEN A.NAME=N'产品A' AND NOT EXISTS (SELECT 1 FROM test B WHERE A.NAME=B.NAME AND A.RcveDate>B.RcveDate) THEN A.Qty-400 ELSE A.Qty END AS Qty
,A.RcveDate
FROM test A
)
,test2 AS
(
SELECT *
FROM test1 WHERE ID=1 AND ID1=1
UNION ALL
SELECT B.ID,B.ID1,B.name
,CASE WHEN A.name=B.name THEN (CASE WHEN a.qty<0 THEN A.qty+B.qty ELSE b.qty END) ELSE B.Qty END
,B.RcveDate
FROM test2 AS A ,test1 AS B
WHERE B.ID=A.ID+1
)
SELECT name,qty,RcveDate
FROM test2
WHERE qty>0
现在看了下select出来的,但库存的表没变动吧.
-- 写一小段,没有考虑并发和数量不够的情况,如果有需要,LZ 自己改改
drop table tb
go
select 1 as id , '产品A' as name ,200 as number,'2014-05-07' rq into tb
union all
select 2 , '产品A',300,'2014-03-05'
union all
select 3 , '产品B',200,'2014-03-05'
goselect * from tb
go
declare @n_drea int = 400 ; -- 要减少的数量
declare @v_name varchar(30) = '产品A'; -- 产品名
declare @n_temp int = 0 ; --
declare @n_id int = 0; -- 数据表的 PK
while (@n_drea >0 )
begin
select top 1 @n_temp = number , @n_id = id from tb where name = @v_name order by rq
-- print @n_temp
if @n_drea > @n_temp
begin
delete tb where id = @n_id ;
set @n_drea = @n_drea - @n_temp ;
end
else
begin
update tb set number = @n_temp - @n_drea where id = @n_id ;
set @n_drea = 0 ;
end
end
go
select * from tb
go
(3 行受影响)
id name number rq
----------- ----- ----------- ----------
1 产品A 200 2014-05-07
2 产品A 300 2014-03-05
3 产品B 200 2014-03-05(3 行受影响)
(1 行受影响)(1 行受影响)
id name number rq
----------- ----- ----------- ----------
1 产品A 100 2014-05-07
3 产品B 200 2014-03-05(2 行受影响)
SELECT 名称,abs((sum(数量)-400)),max(入库日期)
FROM A_P
GROUP BY 名称
因为你除了减库存,还需要生成出库单记录,用的是同一个循环逻辑。
出库单
名称 数量 批次
产品A 200 2014-05-07
产品A 200 2014-03-05