-------最近小弟写了几个存储过程，望各位有兴趣可测试一下（最好插入大量数据，测试一下速度)------
-------有错之处，还望各位高手加以指正！ 小弟在此深表感谢！
-------说实话：我都看不懂牛人写的嵌套子查询函数，牛人就是牛人嘛，小弟会努力的..........-------
-------在动手的同时：向牛人学习！向速度学习！向小F、小梁、Tony、Josy等牛人学习
-------最近确实跟他们学到了不少知识，在此再谢谢！
-------但有一点：大部分人认为游标比SQL语句要慢，那就快快动手吧........，实践是检验真理的唯一标准！------
-------原帖网址：
http://topic.csdn.net/u/20091009/09/1f801515-d0c7-4cef-901c-943cfb332baf.html
-
SQL code
-
-- -----------------------------------------------------
-- --- 进出仓结存----修正版(函数版) ------------
-- --------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
------------------------牛人搞定的：嵌套子查询函数----------------------------------------------------
-
SQL code
-
-- >创建表值函数
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
-------------------------还有：数据多了，两者的结果并非完全一样------------------------
-
SQL code
-
-- ----验证数据代码：
/*
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' )
*/
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
