Sql很难解决的算法--（急）

Question

CG(采购表)
id(单据id) Aid(商品id)    outlet_id(门店id)      gys_id(供应商id)   csl(采购数量)
   1         1 1        1                   10
   2         1 1               1 10RK(入库表)
id(单据id)           Aid(商品id)    outlet_id(门店id)      gys_id(供应商id)   rsl(入库数量)
   1                      1                  1                      1                  9
   2                      1                  1                      1                  5
   3                      1                  1                      1                  3
   4                      1                  1                      1                  1现在我要根据采购的数量csl和入库数量rsl计算出那个单居未到货的单居插入到未到货表中
（举例）上面的数据应该生成一条这样的未到货数据，因为单据（采购单）1采购数量10由入库单据（1）提供9个
和入库单据2提供1个，所以采购单据2的csl(10个)由入库单据2，3，4分别入库4，3，1个商品共8个，因此是未到货
数据由此生成下面一条数据
id(采购单据id) Aid(商品id)    outlet_id(门店id)      gys_id(供应商id)   memo(采购数量来源)
    2               1                  1                      1            2[4]3[3]4[1]  关键在于memo字段要计算出未到货的采购数据的入库记录 
2[1]3[3]4[1]：说明本条数据是有入库单据2，3，4分别入库4，3，1个商品

Answer 1

效率不怎么样，try：create function f_str(@id int,@Aid int,@outlet_id int,@gys_id int,@csl int)
returns varchar(100)
as
begin
    declare @ret varchar(100),@sum int
    
    set @ret=''    select @sum=sum(csl) from CG where id<@id and Aid=@Aid and outlet_id=@outlet_id and gys_id=@gys_id
    
    select 
        @ret=@ret+case when p.[sum]>=@sum then rtrim(t.id)+'['+rtrim(t.rsl)+']'
                       when p.[sum]+p.rsl>=@sum then rtrim(t.id)+'['+rtrim(p.[sum]+p.rsl-@sum)+']'
                       else '' 
                  end
    from
        (select t.*,(select sum(rsl) as [sum] from RK t where id<t.id and Aid=t.Aid and outlet_id=t.outlet_id and gys_id=t.gys_id) from RK) p
    where
        p.Aid=@Aid and p.outlet_id=@outlet_id and p.gys_id=@gys_id 
    
    
    return @ret
end
go
select 
    a.id,a.Aid,a.outlet_id,a.gys_id,dbo.f_str(a.id,a.Aid,a.outlet_id,a.gys_id,a.csl) as memo
from
    CG a,
    (select Aid,outlet_id,gys_id,sum(rsl) as rsl from RK group by Aid,outlet_id,gys_id) b
where
    a.Aid=b.Aid and a.outlet_id=b.outlet_id and a.gys_id=b.gys_id
    and
    b.rsl<(select sum(csl) from CG where a.Aid=Aid and a.outlet_id=outlet_id and a.gys_id=gys_id and a.id>=id)

Answer 2

LiBin_OberthurCS(子陌红尘) ( ) 信誉：100 
-----------------------------------------------
哈哈，谢谢，我测试一下

Answer 3

LiBin_OberthurCS(子陌红尘) ( ) 信誉：100 
-----------------------------------------------服务器: 消息 107，级别 16，状态 2，过程 f_str，行 11
列前缀 't' 与查询中所用的表名或别名不匹配。

Answer 4

create function f_str(@id int,@Aid int,@outlet_id int,@gys_id int,@csl int)
returns varchar(100)
as
begin
    declare @ret varchar(100),@sum int
    
    set @ret=''    select @sum=sum(csl) from CG where id<@id and Aid=@Aid and outlet_id=@outlet_id and gys_id=@gys_id
    
    select 
        @ret=@ret+case when p.[sum]>=@sum then rtrim(t.id)+'['+rtrim(t.rsl)+']'
                       when p.[sum]+p.rsl>=@sum then rtrim(t.id)+'['+rtrim(p.[sum]+p.rsl-@sum)+']'
                       else '' 
                  end
    from
        (select t.*,(select sum(rsl) as [sum] from RK where id<t.id and Aid=t.Aid and outlet_id=t.outlet_id and gys_id=t.gys_id) from RK t) p
    where
        p.Aid=@Aid and p.outlet_id=@outlet_id and p.gys_id=@gys_id 
    
    
    return @ret
end
go

Answer 5

LiBin_OberthurCS(子陌红尘) ( ) 信誉：100 
-----------------------------------------------创建函数时出现下列问题
//////////////////////////
服务器: 消息 107，级别 16，状态 2，过程 f_str，行 11
列前缀 't' 与查询中所用的表名或别名不匹配。
服务器: 消息 107，级别 16，状态 1，过程 f_str，行 11
列前缀 't' 与查询中所用的表名或别名不匹配。
服务器: 消息 107，级别 16，状态 1，过程 f_str，行 11
列前缀 't' 与查询中所用的表名或别名不匹配。

Answer 6

playwarcraft() ( ) 信誉：100  2006-06-22 12:44:00  得分: 0  
 
 
   請問,樓主,是否還要考慮其他3個欄位,
商品,門店,供應商????
--------------------------------------------------
谢谢！不用考虑
  
 

Answer 7

fcuandy(要学的东西还很多) ( ) 信誉：100  2006-06-22 12:55:00  得分: 0  
 
 
   最近好像理解能力有问题了，看到汉字描述的要求就犯傻
  -------------------------------------------------------------------------
哈哈！谢谢参与，我的表达不是太完善，不好意思了
 

Answer 8

fcuandy(要学的东西还很多) ( ) 信誉：100  2006-06-22 13:08:00  得分: 0  
 
 
   刚才细看了一下。楼主的意思我是明白了。
  
 
-------------------------------------------
太好了，帮帮忙吧！哈哈

Answer 9

尽力做了一下create table CG(id int identity(1,1),Aid int,outlet_id int,gys_id int,csl int)
insert CG select 1,1,1,10
union all select 1,1,1,10
GOcreate table RK(id int identity(1,1),Aid int,outlet_id int,gys_id int,rsl int)
insert RK select 1,1,1,9
union all select 1,1,1,5
union all select 1,1,1,3
union all select 1,1,1,1
GO--处理返回字符串
CREATE FUNCTION f_1(@CGid int,@Aid int)
RETURNS varchar(100)
AS
BEGIN
   declare @ret varchar(100),@inCount intselect @inCount= sum(a.csl) from (select * from CG where id<=@CGid) a group by Aid
set @ret=''select @ret=@ret+convert(varchar,b.id)+'['+
convert(varchar,case when b.rslSum>@inCount+r.rsl then  r.rsl else b.rslSum-@inCount end)+
']' from 
(
select id,sum(rsl) [rslSum] from
(
select r1.id,r2.rsl from RK r1
join RK r2 on r1.Aid=r2.Aid and r1.id>=r2.id
) a group by a.id
having sum(rsl)>@inCount) b 
join RK r on r.id=b.id   return @ret
END
GO--确定需要有返回字符串的情况
CREATE FUNCTION f_2(@CGid int,@Aid int)
RETURNS varchar(100)
AS
BEGIN
   declare @ret varchar(100)
if(select sum(rsl) from RK group by Aid having Aid=@Aid)>= 
(select sum(a.csl) from (select * from CG where id<=@CGid) a group by Aid)
   --如果大于则不用查询出来
   select @ret=''
   else
   begin
   select top 1 @CGid=id from RK where id<@CGid order by id desc
   select @ret=dbo.f_1(@CGid,@Aid)
   end
   return @ret
END
GOselect dbo.f_2(id,aid) [result] from CG where  dbo.f_2(id,aid) <> ''
--如果可以满足有剩余入库则为''，否则存在字符串
/*
result
------------------------------
2[4]3[3]4[1]
*/
drop function dbo.f_2
drop function dbo.f_1
drop table RK
drop table CG

Answer 10

create function f_str(@id int,@Aid int,@outlet_id int,@gys_id int,@csl int)
returns varchar(100)
as
begin
    declare @ret varchar(100),@sum int
    
    set @ret=''    select @sum=sum(csl) from CG where id<@id and Aid=@Aid and outlet_id=@outlet_id and gys_id=@gys_id
    
    select 
        @ret=@ret+case when p.[sum]>=@sum then rtrim(p.id)+'['+rtrim(p.rsl)+']'
                       when p.[sum]+p.rsl>=@sum then rtrim(p.id)+'['+rtrim(p.[sum]+p.rsl-@sum)+']'
                       else '' 
                  end
    from
        (select t.*,(select sum(rsl) as [sum] from RK where id<t.id and Aid=t.Aid and outlet_id=t.outlet_id and gys_id=t.gys_id) from RK t) p
    where
        p.Aid=@Aid and p.outlet_id=@outlet_id and p.gys_id=@gys_id 
    
    
    return @ret
end
go

Answer 11

Sorry ，没有许可，不能安装测试环境，所以提供的代码都没经过测试。

Answer 12

我觉得这题用循环（或function)做的话，应该条理清楚些，并且也比较简单，所以没有循环或函数。
就想了想能不能用几条SQL语句完成，所以多花了点时间，刚才公司断网了，就有事出去了，不过跟楼主的要求有点出入。另外，多佘的字段我没有加上去，你有需要的话，自己加，无非是写语句时两个表多几个关联条件。DECLARE @cg TABLE
(
id int,
csl int
)
INSERT @cg
SELECT    1 , 10
UNION SELECT   2  ,10
UNION SELECT   3  ,15DECLARE @rk TABLE
(
id int,
rsl int
)
INSERT @rk
SELECT
   1,9
UNION SELECT
   2,5
UNION SELECT
   3,6
UNION SELECT
   4,7
DECLARE @tmp_r TABLE(id int,rsl int,rsl_all int)
DECLARE @tmp_c TABLE(id int,csl int,csl_all int)INSERT @tmp_r SELECT id,rsl,rsl_preAll=(SELECT SUM(b.rsl) FROM @rk b WHERE b.id<=a.id) FROM @rk a  
INSERT @tmp_c SELECT id,csl,csl_preAll=(SELECT ISNULL(SUM(b.csl),0) FROM @cg b WHERE b.id<=a.id) FROM @cg aselect a.id rid,rsl,rsl_all,b.id cid,csl,csl_all into #t from @tmp_r a
inner join @tmp_c b
on csl_all<rsl_allselect min(rid) rid,cid from #t group by ciddrop table #t结果
rid cid
2 1
4 2------------------------------------------
结果数据的意思是 在id=2的入库记录时，id=1的采购记录完成。
在id=4的入库记录时,id=2的采购记录完成。
所没能完成的，就是在采购记录中 id>max(cid)的。
对应的在入库记录中 id>max(rid)也就是 rsl加起来也不能凑够未完成那张单子的 csl的记录.
------------------------------------------当存在以下情况时
CG(采购表)
id(单据id) Aid(商品id)    outlet_id(门店id)      gys_id(供应商id)   csl(采购数量)
   1         1 1        1                   10
   2         1 1               1 10
   3         1 1               1 50RK(入库表)
id(单据id)           Aid(商品id)    outlet_id(门店id)      gys_id(供应商id)   rsl(入库数量)
   1                      1                  1                      1                  9
   2                      1                  1                      1                  5
   3                      1                  1                      1                  3
   4                      1                  1                      1                  1
有多条采购记录不能被满足时，不知道你要怎么存表当然，对应的是，超过采购记录多出来多条入库记录时，又怎么存表,
类似于CG(采购表)
id(单据id) Aid(商品id)    outlet_id(门店id)      gys_id(供应商id)   csl(采购数量)
   1         1 1        1                   10
   2         1 1               1 10
RK(入库表)
id(单据id)           Aid(商品id)    outlet_id(门店id)      gys_id(供应商id)   rsl(入库数量)
   1                      1                  1                      1                  12
   2                      1                  1                      1                  5
   3                      1                  1                      1                  23
   4                      1                  1                      1                  12
   5                      1                  1                      1                  55因为不太清楚你要怎么存表，所以给出了这样的结果数据，实际上跟你的意思相比，只是显示的问题，结果已经出来了。

Answer 13

写可以不用这么多表变量或临时表，如果你不怕写子查询麻烦的话，可以一层套一层，从而用一条SQL语句实现这个功能。不过一条语句实现的未必就是好的。

Answer 14

不知道樓主的紀錄多不多,能不能忍受游標得速度.../*----測試環境
CG: id  csl    RK: id rsl
 ----------    ----------
    1   10         1   9
    2   10         2   5
    3   10         3   3
                   4   1
----------------------*/declare @num1 int,@num2 int,@id1 int,@csl int,@id2 int,@rsl int,@num5 int
declare @str1 varchar(500),@str3 varchar(500),@sql varchar(500)
set @num1=0
set @num5=(select max([id]) from RK)
declare c1 cursor scroll for
      select [id],csl from CG 
      open c1
     fetch first from c1 into @id1,@csl
declare c2 cursor scroll for
      select [id],rsl from RK
       open c2
       fetch first from c2 into @id2,@rsl
while @@fetch_status=0
     begin
        while   @@fetch_status=0 and @csl-@rsl>0
             begin
            set  @csl=@csl-@rsl
           fetch next from c2 into @id2,@rsl
           end  
   if @id2=@num5
      break
 fetch next from c1 into @id1,@csl 
     endset @num1=(select min([id])  from RK a where (select sum(csl) from CG where [id]<=@id1)>(select  sum(rsl) from RK where [id]<=a.[id])
     and (select  sum(rsl) from RK where [id]<=a.[id])>(select sum(csl) from CG where [id]<@id1))
set @num2=(select sum(rsl) from RK where [id]<=@num1)-(select sum(csl) from CG where [id]<@id1)
set @str3=RTrim(convert(char(03),@num1))+'['+RTrim(convert(char(04),@num2))+']'select * into #temp from ( select [id],rsl from RK where [id]>@num1) A
set @str1=''
select @str1=@str1+RTrim(convert(char(02),[id]))+'['+Rtrim(convert(char(03),rsl))+']'  from #temp
set @str1=@str3+@str1
close c2
deallocate c2
close c1
deallocate c1set @sql='select [id],'+char(39)+@str1+char(39)+' as memo  from CG where [id]='+Rtrim(convert(char(02),@id1))+' union select [id],Rtrim(convert(char(08),csl))  from CG where [id]>'+Rtrim(convert(char(02),@id1))exec (@sql)
drop table  #temp
/*--The result:*/
 
(2 row(s) affected)
id          memo         
----------- ------------ 
2           2[4]3[3]4[1]
3           10 

Answer 15

楼主的表结构设计有问题，ERP中所有的流水式操作必须有关联关系把相关数据链接起来。并且有冗余字段存放每一步处理结果或者标志。要有表头和表单两个表。要有输入人、输入时间、审核人、审核时间、（修改人、修改时间）等字段。
楼主的这个例子充分说明了科学设计的重要性。

Answer 16

--下列語句是實現所有采購的分配情況(不管是否結案)
if exists(select 1 from sysobjects where object_id('CG')=id and type='U')
drop table CGif exists(select 1 from sysobjects where object_id('RK')=id and type='U')
drop table RK
gocreate table CG(id int not null identity(1,1),Aid int,outlet_id int,gys_id int,csl int)
create table RK(id int not null identity(1,1),Aid int,outlet_id int,gys_id int,rsl int)
goif exists(select * from sysobjects where object_id('f_str')=id and type='FN')
drop function f_str
gocreate function f_str(@id int,@Aid int,@outlet_id int,@gys_id int,@csl int,@is_have_kc bit)
returns varchar(100)
as
begin
    declare @ret varchar(100),@sum int,@sum2 int
    
    set @ret=''    select @sum=isnull(sum(csl),0) from CG where id<@id and Aid=@Aid and outlet_id=@outlet_id and gys_id=@gys_id
    select @sum2=@sum+@csl
    
    if @is_have_kc=0 
select 
@ret=@ret+case when p.[sum]>=@sum then rtrim(p.id)+'['+rtrim(p.rsl)+']'
               when p.[sum]+p.rsl>@sum then rtrim(p.id)+'['+rtrim(p.[sum]+p.rsl-@sum)+']'
               else '' 
          end
from
(select t.*,(select isnull(sum(rsl),0)  from RK where id<t.id and Aid=t.Aid and outlet_id=t.outlet_id and gys_id=t.gys_id) as [sum] from RK t) p
where
p.Aid=@Aid and p.outlet_id=@outlet_id and p.gys_id=@gys_id 
    else
select 
@ret=@ret+case when p.[sum]<=@sum and p.[sum]+p.rsl>@sum then case when p.[sum]+p.rsl<=@sum2 then rtrim(p.id)+'['+rtrim(p.rsl-(@sum-p.[sum]))+']' else  rtrim(p.id)+'['+rtrim(@sum2-@sum)+']' end
               when p.[sum]>@sum and p.[sum]<@sum2 then case when p.[sum]+p.rsl<=@sum2 then rtrim(p.id)+'['+rtrim(p.rsl)+']' else  rtrim(p.id)+'['+rtrim(@sum2-p.[sum])+']' end
               else '' 
          end
from
(select t.*,(select isnull(sum(rsl),0)  from RK where id<t.id and Aid=t.Aid and outlet_id=t.outlet_id and gys_id=t.gys_id) as [sum] from RK t) p
where
p.Aid=@Aid and p.outlet_id=@outlet_id and p.gys_id=@gys_id     
    return @ret
end
goinsert CG
select 1,1,1,10 union all
select 1,1,1,10 union all
select 1,1,1,10 union all
select 1,1,1,10 insert RK
select 1,1,1,9 union all
select 1,1,1,3 union all
select 1,1,1,6 union all
select 1,1,1,13 union all
select 1,1,1,7 
goselect * from CG
/*
id Aid outlet_id gys_id  csl
1 1 1 1 10
2 1 1 1 10
3 1 1 1 10
4 1 1 1 10
*/
select * from RK
/*
id Aid outlet_id gys_id  csl
1 1 1 1 9
2 1 1 1 3
3 1 1 1 6
4 1 1 1 13
5 1 1 1 7
*/select 
    a.id,a.Aid,a.outlet_id,a.gys_id,dbo.f_str(a.id,a.Aid,a.outlet_id,a.gys_id,a.csl,case when b.rsl>=(select sum(csl) from CG where a.Aid=Aid and a.outlet_id=outlet_id and a.gys_id=gys_id and a.id>=id) then 1 else 0 end) as memo
from
    CG a,
    (select Aid,outlet_id,gys_id,sum(rsl) as rsl from RK group by Aid,outlet_id,gys_id) b
where
    a.Aid=b.Aid and a.outlet_id=b.outlet_id and a.gys_id=b.gys_id
/*
id Aid outlet_id gys_id  memo
1 1 1 1 1[9]2[1]
2 1 1 1 2[2]3[6]4[2]
3 1 1 1 4[10]
4 1 1 1 4[1]5[7]
*/