IDC资讯中心
/*–示例
–出处:邹建
在代码中，首先定义了一个最简单的出入库数据记录明细表（tb），用来记录每笔出入库的交易情况。明细帐查询要求得到每种item每天的期初数量、当天进货数、进货退回数、出货数、出货退回数及当天结余数。–*/
–明细帐数据create table tb(id int identity primary key,item varchar(10), –产品编号quantity int, –交易数量flag bit, –交易标志,1代表入库,0代表出库,这样可以有效区分退货(负数)date datetime) –交易日期insert tb select aa,100,1,2005-1-1union all select aa,90 ,1,2005-2-1union all select aa,55 ,0,2005-2-1union all select aa,-10,1,2005-2-2union all select aa,-5 ,0,2005-2-3union all select aa,200,1,2005-2-2union all select aa,90 ,1,2005-2-1union all select bb,95 ,1,2005-2-2union all select bb,65 ,0,2005-2-3union all select bb,-15,1,2005-2-5union all select bb,-20,0,2005-2-5union all select bb,100,1,2005-2-7union all select cc,100,1,2005-1-7go
–select * from tb
–结果
id item quantity flag date ———– ———- ———– —- —————————————————— 1 aa 100 1 2005-01-01 00:00:00.0002 aa 90 1 2005-02-01 00:00:00.0003 aa 55 0 2005-02-01 00:00:00.0004 aa -10 1 2005-02-02 00:00:00.0005 aa -5 0 2005-02-03 00:00:00.0006 aa 200 1 2005-02-02 00:00:00.0007 aa 90 1 2005-02-01 00:00:00.0008 bb 95 1 2005-02-02 00:00:00.0009 bb 65 0 2005-02-03 00:00:00.00010 bb -15 1 2005-02-05 00:00:00.00011 bb -20 0 2005-02-05 00:00:00.00012 bb 100 1 2005-02-07 00:00:00.00013 cc 100 1 2005-01-07 00:00:00.000
–查询时间段定义declare @dt1 datetime,@dt2 datetimeselect @dt1=2005-2-1,@dt2=2005-2-10
–查询–统计时间段内无发生额的数据(如果这个不是查询需要的,去掉这段查询)select item, date=convert(char(10),@dt1,120), opening=sum(case when flag=1 then quantity else -quantity end), [in]=0, [in_retrun]=0, [out]=0, [out_return]=0, balance=sum(case when flag=1 then quantity else -quantity end)from tb awhere date<@dt1 and not exists( select * from tb where item=a.item and date>@dt1 and date<dateadd(day,1,@dt2))group by itemunion all–指定时间段内有交易发生的数据select item, date=convert(char(10),date,120), opening=isnull((select sum(case when flag=1 then quantity else -quantity end) from tb where item=a.item and date<min(a.date)),0), [in]=isnull(sum(case when flag=1 and quantity>0 then quantity end),0), [in_retrun]=isnull(sum(case when flag=1 and quantity<0 then -quantity end),0), [out]=isnull(sum(case when flag=0 and quantity>0 then quantity end),0), [out_return]=isnull(sum(case when flag=0 and quantity<0 then -quantity end),0), balance=isnull((select sum(case when flag=1 then quantity else -quantity end) from tb where item=a.item and date<=max(a.date)),0)from tb awhere date>=@dt1 and date<dateadd(day,1,@dt2)group by convert(char(10),date,120),itemorder by item,date
–结果
item date opening in in_retrun out out_return balance ———- ———- ———– ———– ———– ———– ———– ———– aa 2005-02-01 100 180 0 55 0 225aa 2005-02-02 225 200 10 0 0 415aa 2005-02-03 415 0 0 0 5 420bb 2005-02-02 0 95 0 0 0 95bb 2005-02-03 95 0 0 65 0 30bb 2005-02-05 30 0 15 0 20 35bb 2005-02-07 35 100 0 0 0 135cc 2005-02-01 100 0 0 0 0 100
（所影响的行数为 8 行）
