表A
F_name F_num F_type F_date
AAA 10 入库 2007-06-01
BBB 20 入库 2007-06-01
AAA 1 出库 2007-06-03
AAA 2 出库 2007-06-03
AAA 1 出库 2007-06-04
BBB 3 出库 2007-06-05通过时间段统计期初,期未数,出库数,入库数,结果如下:
比如查询时间为2007-06-01-2007-06-05
F_name 期初库存 入库数 出库数 初未库存
AAA 0 10 4 6
BBB 0 20 3 17
F_name F_num F_type F_date
AAA 10 入库 2007-06-01
BBB 20 入库 2007-06-01
AAA 1 出库 2007-06-03
AAA 2 出库 2007-06-03
AAA 1 出库 2007-06-04
BBB 3 出库 2007-06-05通过时间段统计期初,期未数,出库数,入库数,结果如下:
比如查询时间为2007-06-01-2007-06-05
F_name 期初库存 入库数 出库数 初未库存
AAA 0 10 4 6
BBB 0 20 3 17
sum(case when F_man='張三' then F_num else 0 end)as '張三',
sum(case when F_man='王五' then F_num else 0 end)as '王五'from t
group by F_name
F_name varchar(10),
F_num int,
F_type varchar(10),
F_date varchar(10))insert @t select 'AAA', 10, '入库', '2007-06-01'
union all select 'BBB', 20, '入库', '2007-06-01'
union all select 'AAA', 1, '出库', '2007-06-03'
union all select 'AAA', 2, '出库', '2007-06-03'
union all select 'AAA', 1, '出库', '2007-06-04'
union all select 'BBB', 3, '出库', '2007-06-05'
select F_name, '期初库存' = 0,
sum(case F_type when '入库' then F_num else 0 end) as '入库数',
sum(case F_type when '出库' then F_num else 0 end) as '出库数',
'期末库存' = sum(case F_type when '入库' then F_num else 0 end)
-sum(case F_type when '出库' then F_num else 0 end)
from @t
group by F_name
/*
F_name 期初库存 入库数 出库数 期末库存
---------- ----------- ----------- ----------- -----------
AAA 0 10 4 6
BBB 0 20 3 17
*/
insert into tb values('BBB', 20, '入库', '2007-06-01')
insert into tb values('BBB', 20, '入库', '2007-06-01')
insert into tb values('AAA', 2 , '出库', '2007-06-03')
insert into tb values('AAA', 1 , '出库', '2007-06-04')
insert into tb values('BBB', 3 , '出库', '2007-06-05')select t1.f_name , 期初库存 = 0 , t1.入库数 , t2.出库数 , t1.入库数 - t2.出库数 期未库存 from
(
select f_name , sum(f_num) 入库数 from tb where F_type = '入库' and f_date >= '2007-06-01' and f_date<= '2007-06-05' group by f_name
) t1
full join
(
select f_name , sum(f_num) 出库数 from tb where F_type = '出库' and f_date >= '2007-06-01' and f_date<= '2007-06-05' group by f_name
) t2
on t1.f_name = t2.f_namedrop table tb/*
f_name 期初库存 入库数 出库数 期未库存
---------- ----------- ----------- ----------- -----------
AAA 0 10 3 7
BBB 0 40 3 37(所影响的行数为 2 行)*/
insert into t select 'AAA',10,'入库','2007-06-01'
UNION all
select 'AAA',10,'入库','2007-06-01'
UNION all
select 'BBB',20,'入库','2007-06-01'
UNION all
select 'AAA',1,'出库','2007-06-03'
UNION all
select 'AAA',2,'出库','2007-06-03'
UNION all
select 'AAA',1,'出库','2007-06-04'
UNION all
select 'BBB',3,'出库','2007-06-05'
select F_name, '期初库存' = 0,
sum(case F_type when '入库' then F_num else 0 end) as '入库数',
sum(case F_type when '出库' then F_num else 0 end) as '出库数',
'期末库存' = sum(case F_type when '入库' then F_num else 0 end)
-sum(case F_type when '出库' then F_num else 0 end)
from t
group by F_name
'0' as 其实库存,
sum(case f_type when '入库' then f_num else 0 end) as 入库数,
sum(case f_type when '出库' then f_num else 0 end) as 出库数,
sum(case f_type when '入库' then f_num else 0 end)-
sum(case f_type when '出库' then f_num else 0 end) as 初未库存
from testdt1 where f_date between '2007-06-01' and '2007-06-03' group by f_name
create table #A
(
F_name varchar(20),
F_num int,
F_type varchar(10),
F_date datetime
)goinsert into #A
select 'AAA',10,'入库','2007-06-01'
union all select
'BBB', 20, '入库', '2007-06-01'
union all select
'AAA', 1 , '出库', '2007-06-03'
union all select
'AAA', 2 , '出库', '2007-06-03'
union all select
'AAA', 1 , '出库', '2007-06-04'
union all select
'BBB', 3 , '出库', '2007-06-05'goselect *,(期初库存+入库数-出库数)期末库存
from(
select F_name,
(isnull((select sum(F_num) from #A where #A.F_name=OO.F_name and F_date<'2007-6-01' and F_type='入库'),0)-isnull((select sum(F_num) from #A where #A.F_name=OO.F_name and F_date<'2007-6-01' and F_type='出库'),0))期初库存,
sum(case when F_type='入库' then F_num else 0 end)入库数,
sum(case when F_type='出库' then F_num else 0 end)出库数
from #A OO
where OO.F_date between '2007-06-01' and '2007-06-05'
group by F_name
)Outest