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

[luoyoumou]

-------最近小弟写了几个存储过程，望各位有兴趣可测试一下（最好插入大量数据，测试一下速度)------
-------有错之处，还望各位高手加以指正！ 小弟在此深表感谢！
-------说实话：我都看不懂牛人写的嵌套子查询函数，牛人就是牛人嘛，小弟会努力的..........-------
-------在动手的同时：向牛人学习！向速度学习！向小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' )
*/

[XGJ889]

jf

[fwacky]

study!Q

[wsxcdx]

学习

[悔说话的哑巴]

学习

[luoyoumou]

--怎么又变成：“(苏格兰折耳鸭)”了？

[dxhsc]

看了一下，还可以，第一种用游标的方式，数据量大了，效率要高点，第二种嵌套太多了，只适合查询小数据量的数据，第一种业务要清楚点

[pbsh]

楼主好强！！

[pbsh]

人齐了，可以结帖了。

[Thr21ough]

没看懂~

[snwgija]

正好要用到，先顶帖再学习``

[luoyoumou]

.

[zhangchaokun]

顶

[Yang_]

.

[yuzhifu1]

顶

[guguda2008]

游标本来就慢，占CPU占IO，如果一个查询只能用游标完成只能说明表设计不合理

[feixianxxx]

牛B

[z415353144]

鸟人

[Dream_1986]

学习

[claro]

.

[Aslangg]

.