----＠呵呵：比的就是速度，看不懂的都是经典＠----

2009-10-17 09:56:33 970 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Matlab special usage ： @ and Struct Background Knowledge 函数句柄：是包含了函数的路径、函数名、类型以及可能存在的重载方法。函数句柄必须通过专门的定义创建的，而一般的图像的句柄是自动建立的。（可理解为函数指针）创建函数句柄使用@或者str2func命令创建 [email=h_fun=@fun]h_fun=@fun[/email]; h_fun=str2func('fun'); 比如说sin 阅读详情

-------最近小弟写了几个存储过程，望各位有兴趣可测试一下（最好插入大量数据，测试一下速度)------
-------有错之处，还望各位高手加以指正！小弟在此深表感谢！
-------说实话：我都看不懂牛人写的嵌套子查询函数，牛人就是牛人嘛，小弟会努力的..........-------
-------在动手的同时：向牛人学习！向速度学习！向小F、小梁、Tony、Josy等牛人学习
-------最近确实跟他们学到了不少知识，在此再谢谢！

-------但有一点：大部分人认为游标比SQL语句要慢，那就快快动手吧........，实践是检验真理的唯一标准！------

-------原帖网址：
http://topic.csdn.net/u/20091009/09/1f801515-d0c7-4cef-901c-943cfb332baf.html

-------------------------------------------------------

-----       进出仓结存----修正版(函数版)   ------------ 

----------Author：Luoyoumou----------------------------

-------------------------------------------------------



alter function inout_func(@fromdate datetime, @todate datetime)

/*

select * from dbo.inout_func( '2009-08-01','2009-08-31' )

select * from dbo.inout_func( '2009-08-01','2009-09-30' )

*/

returns 

   @t table([billid] int,[bd] varchar(10),[bc] varchar(10),[storeid] varchar(4),[goodsid] varchar(5),[qty] int)

as



begin



DECLARE @t2 table (

id int identity(1,1), --自增标识位

billid varchar(4),

bd varchar(10),

bc varchar(10),

storeid varchar(4),

goodsid varchar(5),  

qty int,

sum_out int --指定时间段的出库总数量

);



insert into @t2(billid, bd, bc, storeid, goodsid, qty, sum_out)

select i.billid, i.bd, i.bc, i.storeid, i.goodsid, i.qty,

       isnull(o.sum_out,0) sum_out

  from instored i left join (

        select storeid, goodsid, sum(qty) as sum_out

          from outstored

         where bd >=@fromdate and bd<=@todate 

         group by storeid, goodsid ) o

   on i.storeid=o.storeid and i.goodsid=o.goodsid

  where i.bd >=@fromdate and i.bd<=@todate

order by i.storeid, i.goodsid, i.bd;



--特定仓库，特定货品无出库记录的指定时间段内的入库记录直接插入

insert into @t(billid, bd, bc, storeid, goodsid, qty)

select billid, bd, bc, storeid, goodsid, qty

from @t2 where sum_out=0;





-----------定义变量-------------------------

declare @billid varchar(4)

declare @bd varchar(10)

declare @bc varchar(10)

declare @storeid varchar(4), @storeid2 varchar(4) 

declare @goodsid varchar(5), @goodsid2 varchar(4)

declare @qty int, @in_sumQty int, @sum_out int

declare @flag int



set @storeid2=''

set @goodsid2=''



--游标检索

DECLARE @MyData CURSOR 

SET @MyData = CURSOR FOR 

    SELECT billid, bd, bc, storeid, goodsid, qty, sum_out from @t2

    where sum_out<>0 order by Id

OPEN @MyData

FETCH NEXT FROM  @MyData INTO @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out

WHILE @@FETCH_STATUS = 0 

BEGIN

    IF(@storeid2<>@storeid or @goodsid2<>@goodsid) --如果仓库编码或物料编码发生变化(没有细分是哪种情况了)

    BEGIN

        SET @storeid2=@storeid --重新初始化仓库编码变量

        SET @goodsid2=@goodsid --重新初始化物料编码变量

        SET @in_sumQty=0;      --重新初始化进库存总数变量

        SET @flag=0;           --重新初始化标志位

    END



    SET @in_sumQty=@in_sumQty+@qty;



    IF(@in_sumQty>@sum_out) 

    BEGIN

        IF(@flag=0)

        BEGIN  ----表示第一次查找到符合条件的将要拆分的记录行

            INSERT INTO @t(billid, bd, bc, storeid, goodsid, qty) 

            VALUES(@billid, @bd, @bc, @storeid, @goodsid, @in_sumQty-@sum_out);

            SET @flag=1;

        END

        ELSE  -----表示第二次或以上查找到符合条件的记录行(整条记录行都当作剩余的库存)

        BEGIN

            INSERT INTO @t(billid, bd, bc, storeid, goodsid, qty) 

            VALUES(@billid, @bd, @bc, @storeid, @goodsid, @qty);    

        END

    END

    FETCH NEXT FROM  @MyData INTO @billid, @bd, @bc, @storeid, @goodsid, @qty, @sum_out

END 

CLOSE @MyData 

DEALLOCATE @MyData 



return



end

------------------------牛人搞定的：嵌套子查询函数----------------------------------------------------

-->创建表值函数

create function f_fifo(@start varchar(10),@end varchar(10))

returns 

   @t table([billid] int,[bd] varchar(10),[bc] varchar(9),[storeid] varchar(3),[goodsid] varchar(4),[qty] int)

as

begin

insert @t

select 

  billid,convert(varchar(10),bd,120),bc,storeid,goodsid,

  case when iqty>qty then qty else iqty end  as qty

from(

select 

  a.billid,a.bd,a.bc,a.storeid,a.goodsid,a.qty as iqty,

  (select sum(qty) 

   from (select 

           *,

           px=(select count(1) 

               from instored 

               where storeid=i.storeid  and goodsid=i.goodsid  and (bd<i.bd or bd=i.bd and billid<i.billid) and convert(varchar(10),bd,120) between @start and @end) 

         from instored i where convert(varchar(10),bd,120) between @start and @end)t 

   where storeid=a.storeid and goodsid=a.goodsid  and px<=a.px

  )-isnull(b.qty,0) as qty



from

  (select 

           *,

           px=(select count(1) 

               from instored 

               where storeid=i.storeid and goodsid=i.goodsid and (bd<i.bd or bd=i.bd and billid<i.billid) and convert(varchar(10),bd,120) between @start and @end) 

         from instored i where convert(varchar(10),bd,120) between @start and @end) a

left join

  (select storeid,goodsid,sum(qty) as qty from [outstored] where convert(varchar(10),bd,120) between @start and @end group by storeid,goodsid) b

on 

  a.storeid=b.storeid and a.goodsid=b.goodsid 

) tt

where qty>0

order by 

  storeid,goodsid

  return

end

go



-->查询1

SELECT * FROM dbo.f_fifo('2009-08-01','2009-08-31')

/**

billid      bd         bc        storeid goodsid qty

----------- ---------- --------- ------- ------- -----------

9           2009-08-20 090820002 a仓      g001    200

3           2009-08-23 090823001 b仓      g001    600

5           2009-08-23 090823003 b仓      k002    1900



(3 行受影响)

**/



-->查询2

SELECT * FROM dbo.f_fifo('2009-08-01','2009-09-30')

/**

billid      bd         bc        storeid goodsid qty

----------- ---------- --------- ------- ------- -----------

6           2009-09-26 090926001 a仓      g001    200

3           2009-08-23 090823001 b仓      g001    600

5           2009-08-23 090823003 b仓      k002    900

7           2009-09-27 090927001 b仓      k002    400

-------------------------还有：数据多了，两者的结果并非完全一样------------------------



------验证数据代码：

/*

select sum(qty) from (SELECT * FROM dbo.f_fifo('2009-09-01','2009-09-30')) t

select (select sum(qty) from Instored where bd<'2009-08-01')-(select sum(qty) from Outstored where bd<'2009-08-01')



exec inout_proc '2009-08-01','2009-08-31'

exec inout_proc '2009-08-01','2009-09-30'

select top 1 * from instored;

select (select sum(qty) from Instored where bd>='2009-08-01' and bd<='2009-09-30')-(select sum(qty) from Outstored where bd>='2009-08-01' and bd<='2009-09-30')

*/

/*

select sum(qty) from (select * from dbo.inout_func( '2009-08-01','2009-09-30' )) t

select * from dbo.inout_func( '2009-08-01','2009-09-30' )

*/

...全文