--貌似可以將就用用?出去玩了一圈,發現腦袋要銹豆了
create table t ([month] int ,flag varchar(02),qty int)
insert into t
select 1,'收',1 union all
select 1,'收',2 union all
select 1,'付',2 union all
select 2,'收',1 union all
select 3,'付',1
go
declare @t table (flag varchar(02))
insert into @t select '收' union all select '付' union all select '存'
select * into #t from @t,(select distinct [month] from t) tt
select a.[month],a.flag,isnull(sum ( b.qty ),0) as qty
into #tt
from #t a left join t b
on a. flag=b.flag and a.[month]=b.[month]
group by a.[month],a.flag
order by a.[month],case when a.flag='收' then 1 when a.flag='付' then 2 else 3 end
update #tt set qty=isnull((select a.qty from #tt a where a.[month]=#tt.[month] and a.flag='收'),0)-isnull((select a.qty from #tt a where a.[month]=#tt.[month] and a.flag='付'),0)
where flag='存'
/*result:*/
select * from #tt
drop table t
drop table #t
drop table #tt
select @c=max(月份) from @t
while @c>0
begin
insert #t values(@c,'收',0)
insert #t values(@c,'付',0)
insert #t values(@c,'存',0)
set @c=@c-1
end
select 月份,标记,数量=sum(数量) into # from @t group by 月份,标记
update #t set #t.数量= #.数量 from # where
#t.月份=#.月份 and #t.标记=#.标记
update #t set 数量=isnull((select top 1 数量 from # where 月份=#t.月份 and 标记='收' order by 数量 ),0)
-isnull((select top 1 数量 from @t t where 月份=#t.月份 and 标记='付' order by 数量 ),0)
where 标记='存'
set nocount off
select * from #t order by 月份,case 标记 when '收' then 0 when '付' then 1 else 2 end