请教先进先出的高效算法sql

[zjl8008]

要求，库存表按入库日期先进先出，不允许出再负数库存出库时同时只从一个药房（如yfbh=’01’）操作，出库药品（ypbh）数量n（cksl）个，
这时01库可能有几个批次的药品合计>n,自动从这几行记录中减少kcsl,kcsl为0的记录可以删除.
同时记录到出库明细表中去。
我以前都是在前台程序中 用循环等方法处理的，求一个用sql语句或存储过程(药房（如yfbh=’01’），出库药品（ypbh）,数量n)的高效方法

Sql2005 库存表 ，id为自增列，yfbh,ypbh,rkls,ph 这4列组成唯一约束

create table dbo.yk_yk (
id bigint identity(1, 1),
yfbh varchar(20) collate Chinese_PRC_CI_AS null,--药房编号
ypbh varchar(20) collate Chinese_PRC_CI_AS null,--药品编号
rkls varchar(20) collate Chinese_PRC_CI_AS null,--入库流水
ph varchar(50) collate Chinese_PRC_CI_AS null,--批号
kcsl money null, --库存数
rkrq datetime null –入库日期
constraint PK_YK_YK primary key (id)
on "PRIMARY"
)
Go
出库明细表 ckid为自增列，主键 无其他约束
create table dbo.yp_ck_mz (
ckid bigint identity(1, 1),
ypbh varchar(20) collate Chinese_PRC_CI_AS not null,
sl money not null,
rq datetime not null,
yfbh varchar(20) collate Chinese_PRC_CI_AS null,
rkls varchar(20) null,
ph varchar(50) collate Chinese_PRC_CI_AS null,
rkrq datetime not null

)

[zjl8008]

[Quote=引用 1 楼 qianjin036a 的回复:]
你可以考虑用公用表达式进行递归.
[/Quote]
能给个例子吗？

[sekai2011]

mark ...

[--小F--]

--库存先进先出简单例子：

create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int,        --入库数量
c int,        --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select  'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select  'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select  'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select  'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select  'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select  'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select  'A',510,0,'2008-03-01'
go



create proc wsp
@name varchar(50),--商品名称
@cost int         --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name 
if(@spare>=@cost)
begin
    --根据入库日期采用先进先出原则对货物的库存进行处理
    update t set c=
    case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
    then a.j 
    else 
        case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0 
        else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c) 
        end 
    end
    from t a where name=@name and j!=c 
end
else
    raiserror('库存不足',16,1)    
    return 
go


--测试：

exec wsp @name='A',@cost=180
select * from t


--drop table t
--drop proc wsp

[-晴天]

你可以考虑用公用表达式进行递归.

[zjl8008]

补充：库存表中，同一入库流水rkls的入库日期是相同的，ph中可以不同，这时ypbh相同的可以按批号升序 排序出库

[AcHerat]

--先进先出！
create table paylog (
           gold int not null,
           paydate datetime not null,
           des  nvarchar(10) not null
             )
insert into paylog
select '80','2010-09-10 13:18','付费'
union all
select '100','2010-09-11 14:18','赠送'
union all
select '30','2010-09-12 14:20','赠送'
union all
select '40','2010-09-13 14:20','付费'
union all
select '90','2010-09-14 14:20','付费'
union all
select '30','2010-09-15 14:20','赠送'
go

create table costlog (
           gold int not null,
           costdate datetime not null
       )
insert into costlog
select '50','2010-09-12 14:00'
union all
select '80','2010-09-13 14:18'
union all
select '10','2010-09-14 14:20'
union all
select '60','2010-09-15 14:20'
union all
select '50','2010-09-16 14:20'
union all
select '1000','2010-09-17 14:20'
--结果
/*
gold  paydate                des     gold costdate                details
80    2010-09-10 13:18:00.000    付费    50    2010-09-12 14:00:00.000    50
80    2010-09-10 13:18:00.000    付费    80    2010-09-13 14:18:00.000    30
100    2010-09-11 14:18:00.000    赠送    80    2010-09-13 14:18:00.000    50
100    2010-09-11 14:18:00.000    赠送    10    2010-09-14 14:20:00.000    10
100    2010-09-11 14:18:00.000    赠送    60    2010-09-15 14:20:00.000    40
30    2010-09-12 14:20:00.000    赠送    60    2010-09-15 14:20:00.000    20
30    2010-09-12 14:20:00.000    赠送    50    2010-09-16 14:20:00.000    10
40    2010-09-13 14:20:00.000    付费    50    2010-09-16 14:20:00.000    40
90    2010-09-14 14:20:00.000    付费    1000    2010-09-17 14:20:00.000    90
30    2010-09-15 14:20:00.000    赠送    1000    2010-09-17 14:20:00.000    30
*/




1：

declare @i int
set @i=1
while @i<=2000
begin

insert into num
select @i

set @i=@i+1
end


go

;with pay_t as (

select row_number() over (order by paydate) as indexno,a.*
from paylog a,num b where a.gold>=b.indexno
),cost_t as (
select row_number() over (order by costdate) as indexno,a.*
from costlog a,num b where a.gold>=b.indexno
)

select a.gold,a.paydate,a.des,b.gold,b.costdate,count(1) as details
from pay_t a,cost_t b where a.indexno=b.indexno group by a.gold,a.paydate,a.des,b.gold,b.costdate
order by b.costdate,a.paydate

--这个实现比较容易，但不适合小数。

2：

with t1 as
(
    select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from paylog where paydate <= a.paydate) from paylog a
)
,t2 as
(
    select id = row_number() over(order by getdate()),*,sumgold=(select sum(gold) from costlog where costdate <= a.costdate) from costlog a
)
,t3 as
(
    select a.*,bid1=b.id
    from t1 a 
    cross apply (select top (1) * from t2 where sumgold >= a.sumgold order by sumgold) b
)
,t4 as
(
    select a.*,bid2=isnull(b.bid1,1) from t3 a left join t3 b on a.id = b.id + 1
)
,t5 as
(
    select paygold=a.gold,a.paydate,a.des,costgold=b.gold,b.costdate,details=
        case 
            when a.sumgold>=b.sumgold then 
                case when a.gold-a.sumgold+b.sumgold >= b.gold then b.gold else a.gold-a.sumgold+b.sumgold end
            else 
                case when b.gold-b.sumgold+a.sumgold >= a.gold then a.gold else b.gold-b.sumgold+a.sumgold end 
        end
    from t4 a join t2 b on b.id between a.bid2 and a.bid1
) 
select * from t5 where details > 0

--这个大家容易迷糊，但弄懂不难，主要是效率有点低！

3：

create table cun(gold int,paydate datetime,[des] nvarchar(10),go int,costdate datetime,details int)
go

declare my_cursor cursor scroll
for
select * from paylog
open my_cursor
declare @gold int
declare @paydate datetime
declare @des nvarchar(10)
set @gold = 0
fetch next from my_cursor into @gold,@paydate,@des
while (@@fetch_status = 0)
    begin
        declare next_cursor cursor scroll
        for
        select * from costlog
        open next_cursor
        declare @go int
        declare @costdate datetime
        declare @details int
        declare @gocun int
        declare @decun int
        set @decun = 0
        set @go = 0
        set @details = 0
        set @gocun = @gold
        fetch next from next_cursor into @go,@costdate
        set @gocun = @gocun - @go
        while(@@fetch_status = 0)
            begin
                if(@gocun = 0)
                begin
                    set @details = @gold
                    insert into cun select @gold,@paydate,@des,@go,@costdate,@details
                    fetch next from my_cursor into @gold,@paydate,@des
                    fetch next from next_cursor into @go,@costdate
                    set @gocun = @gold - @go
                    set @decun = 0
                end
                if(@gocun > 0)
                begin
                    set @details = @go - @decun
                    insert into cun select @gold,@paydate,@des,@go,@costdate,@details
                    fetch next from next_cursor into @go,@costdate
                    set @gocun = @gocun - @go
                    set @decun = 0
                end
                if(@gocun < 0)
                begin
                    set @details = @go + @gocun - @decun
                    insert into cun select @gold,@paydate,@des,@go,@costdate,@details
                    fetch next from my_cursor into @gold,@paydate,@des
                    set @gocun = @gold + @gocun
                    set @decun = @details
                end
            end
        close next_cursor
        deallocate next_cursor
    end
close my_cursor
deallocate my_cursor
select * from cun

--这个用游标写的，代码很简单，主要在于里边运算的一些逻辑！

[GoAwayZ]

用游标实现。

[Q315054403]

虽然现实中有这个需求，但这个需求其实是不合理的
药品在库存里，至于先拿哪个批次，是库管员的事。。。而非系统的事
非正常需求。。当然，现实中非正常需求太多

只能按批次存入库存，再游标或变通处理

[rucypli]

库存先进先出简单例子：

create table t(
id int identity(1,1),
name varchar(50),--商品名称
j int,        --入库数量
c int,        --出库数量
jdate datetime --入库时间
)
insert into t(name,j,c,jdate) select  'A',100,0,'2007-12-01'
insert into t(name,j,c,jdate) select  'A',200,0,'2008-01-07'
insert into t(name,j,c,jdate) select  'B',320,0,'2007-12-21'
insert into t(name,j,c,jdate) select  'A',100,0,'2008-01-15'
insert into t(name,j,c,jdate) select  'B',90,0,'2008-02-03'
insert into t(name,j,c,jdate) select  'A',460,0,'2008-02-01'
insert into t(name,j,c,jdate) select  'A',510,0,'2008-03-01'
go



create proc wsp
@name varchar(50),--商品名称
@cost int         --销售量
as
--先得出该货物的库存是否够
declare @spare float --剩余库存
select @spare=sum(j)-sum(c) from t where name=@name 
if(@spare>=@cost)
begin
    --根据入库日期采用先进先出原则对货物的库存进行处理
    update t set c=
    case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<=a.jdate and j!=c)>=0
    then a.j 
    else 
        case when (select @cost-isnull(sum(j),0)+isnull(sum(c),0) from t where name=@name and jdate<a.jdate and j!=c)<0 then 0 
        else (select @cost-isnull(sum(j),0)+isnull(sum(c),0)+a.c from t where name=@name and jdate<a.jdate and j!=c) 
        end 
    end
    from t a where name=@name and j!=c 
end
else
    raiserror('库存不足',16,1)    
    return 
go


--测试：

exec wsp @name='A',@cost=180
select * from t


--drop table t
--drop proc wsp
 
 
 
对我有用[0] 丢个板砖[0] 引用 举报 管理 TOP  

[zjl8008]

前面的例子方法不错，但需要修改我的表结构，且出库明细记录表没有记录。
看哪位能按我的表结构给个存储过程
create proc proc_ck @yfbh varchar(20),@ypbh varchar(20),@sl money
as
.....

[zjl8008]

多谢几位。看来得我自己写一个存储过程了，偷懒是不行了。。。呵呵