create table test ( no int, year int, month int, account varchar(20), amt int, sign char(1) )insert into test values(2,2003,8,'2100-000-15011000',10,'C') insert into test values(2,2003,8,'2100-000-15012000',20,'C') insert into test values(2,2003,8,'2100-000-15013000',30,'D') insert into test values(4,2003,8,'2100-000-15014000',40,'D') insert into test values(4,2003,8,'2100-000-15015000',50,'C') insert into test values(4,2003,8,'2100-000-15016000',60,'D') insert into test values(4,2003,8,'2100-000-15012000',70,'C') insert into test values(4,2003,8,'2100-000-15014000',80,'D') insert into test values(129,2003,9,'2100-000-10010001',5,'C') insert into test values(130,2003,9,'2100-000-10010001',6,'D') insert into test values(130,2003,9,'2100-000-10010001',7,'C') insert into test values(130,2003,9,'2100-000-10010001',9,'D') --以上部分为建立测试环境create table #temp ( tid int identity(1,1), no int, year int, month int, account varchar(10), amt int, )insert into #temp select no,year,month,substring(account,10,4),(case when sign='C' then amt else -1*amt end) from testdeclare @i int, @cnt intselect @cnt=(select max(cc) from (select count(*) cc from test) t)declare @sql varchar(8000) set @sql='select no,max(year),max(month)' set @i=1while @i<=@cnt begin set @sql=@sql+',sum(case when ord='+cast(@i as varchar)+' then amt else 0 end) as ['+cast(@i as varchar)+']' set @i=@i+1 endset @sql=@sql+' from (select (select count(*) from #temp b where b.no=a.no and b.account=a.account and b.tid<=a.tid) ord,no,year,month,account,amt from #temp a) tt group by no,account order by no'exec(@sql)drop table #temp drop table test
中间有一句有点问题select @cnt=(select max(cc) from (select count(*) cc from #temp group by no,account) t)
--测试--测试数据 create table 表(no int,year int,month int,account varchar(20),amt int,sign char(1)) insert 表 select 2 ,2003,8,'2100-000-15011000',10,'C' union all select 2 ,2003,8,'2100-000-15012000',20,'C' union all select 2 ,2003,8,'2100-000-15013000',30,'D' union all select 4 ,2003,8,'2100-000-15014000',40,'D' union all select 4 ,2003,8,'2100-000-15015000',50,'C' union all select 4 ,2003,8,'2100-000-15016000',60,'D' union all select 4 ,2003,8,'2100-000-15012000',70,'C' union all select 4 ,2003,8,'2100-000-15014000',80,'D' union all select 129,2003,9,'2100-000-10010001',5 ,'C' union all select 130,2003,9,'2100-000-10010001',6 ,'D' union all select 130,2003,9,'2100-000-10010001',7 ,'C' union all select 130,2003,9,'2100-000-10010001',9 ,'D' go--处理 select gid=0,no,year,month,account=substring(account,10,4) ,amt=case sign when 'C' then amt else -amt end into #t from 表 order by no,year,month,accountdeclare @no int,@year int,@month int,@account char(4),@i int,@s varchar(8000) update #t set @i=case when @no=no and @year=year and @month=month and @account=account then @i+1 else 1 end ,gid=@i,@no=no,@year=year,@month=month,@account=accountselect @s='',@i=max(gid) from #t while @i>0 select @s=',['+cast(@i as varchar) +']=sum(case gid when '+cast(@i as varchar) +' then amt else 0 end)'+@s ,@i=@i-1 exec('select no,year,month'+@s+' from #t group by no,year,month') go--删除测试 drop table 表,#t/*--测试结果no year month 1 2 3 4 5 ----------- ----- ----- ----- ----- ----- ---- 2 2003 8 10 20 -30 0 0 4 2003 8 -40 50 -60 70 -80 129 2003 9 5 0 0 0 0 130 2003 9 -6 7 -9 0 0 --*/
(
no int,
year int,
month int,
account varchar(20),
amt int,
sign char(1)
)insert into test values(2,2003,8,'2100-000-15011000',10,'C')
insert into test values(2,2003,8,'2100-000-15012000',20,'C')
insert into test values(2,2003,8,'2100-000-15013000',30,'D')
insert into test values(4,2003,8,'2100-000-15014000',40,'D')
insert into test values(4,2003,8,'2100-000-15015000',50,'C')
insert into test values(4,2003,8,'2100-000-15016000',60,'D')
insert into test values(4,2003,8,'2100-000-15012000',70,'C')
insert into test values(4,2003,8,'2100-000-15014000',80,'D')
insert into test values(129,2003,9,'2100-000-10010001',5,'C')
insert into test values(130,2003,9,'2100-000-10010001',6,'D')
insert into test values(130,2003,9,'2100-000-10010001',7,'C')
insert into test values(130,2003,9,'2100-000-10010001',9,'D')
--以上部分为建立测试环境create table #temp
(
tid int identity(1,1),
no int,
year int,
month int,
account varchar(10),
amt int,
)insert into #temp select no,year,month,substring(account,10,4),(case when sign='C' then amt else -1*amt end) from testdeclare @i int, @cnt intselect @cnt=(select max(cc) from (select count(*) cc from test) t)declare @sql varchar(8000)
set @sql='select no,max(year),max(month)'
set @i=1while @i<=@cnt
begin
set @sql=@sql+',sum(case when ord='+cast(@i as varchar)+' then amt else 0 end) as ['+cast(@i as varchar)+']'
set @i=@i+1
endset @sql=@sql+' from (select (select count(*) from #temp b where b.no=a.no and b.account=a.account and b.tid<=a.tid) ord,no,year,month,account,amt from #temp a) tt group by no,account order by no'exec(@sql)drop table #temp
drop table test
create table 表(no int,year int,month int,account varchar(20),amt int,sign char(1))
insert 表 select 2 ,2003,8,'2100-000-15011000',10,'C'
union all select 2 ,2003,8,'2100-000-15012000',20,'C'
union all select 2 ,2003,8,'2100-000-15013000',30,'D'
union all select 4 ,2003,8,'2100-000-15014000',40,'D'
union all select 4 ,2003,8,'2100-000-15015000',50,'C'
union all select 4 ,2003,8,'2100-000-15016000',60,'D'
union all select 4 ,2003,8,'2100-000-15012000',70,'C'
union all select 4 ,2003,8,'2100-000-15014000',80,'D'
union all select 129,2003,9,'2100-000-10010001',5 ,'C'
union all select 130,2003,9,'2100-000-10010001',6 ,'D'
union all select 130,2003,9,'2100-000-10010001',7 ,'C'
union all select 130,2003,9,'2100-000-10010001',9 ,'D'
go--处理
select gid=0,no,year,month,account=substring(account,10,4)
,amt=case sign when 'C' then amt else -amt end
into #t from 表
order by no,year,month,accountdeclare @no int,@year int,@month int,@account char(4),@i int,@s varchar(8000)
update #t
set @i=case when @no=no and @year=year and @month=month and @account=account
then @i+1 else 1 end
,gid=@i,@no=no,@year=year,@month=month,@account=accountselect @s='',@i=max(gid) from #t
while @i>0
select @s=',['+cast(@i as varchar)
+']=sum(case gid when '+cast(@i as varchar)
+' then amt else 0 end)'+@s
,@i=@i-1
exec('select no,year,month'+@s+' from #t group by no,year,month')
go--删除测试
drop table 表,#t/*--测试结果no year month 1 2 3 4 5
----------- ----- ----- ----- ----- ----- ----
2 2003 8 10 20 -30 0 0
4 2003 8 -40 50 -60 70 -80
129 2003 9 5 0 0 0 0
130 2003 9 -6 7 -9 0 0
--*/
create view test as 后怎么写?