34,590
社区成员
发帖
与我相关
我的任务
分享
create table #Forbug_HTMZje
(id int,xmdc nvarchar(10),xmsj datetime,xmje dec(18,2))
insert #Forbug_HTMZje
select 1,'001014','2011-4-1 0:00:00',1000000.0000 union all
select 2,'001014','2011-5-1 0:00:00',10000.0000 union all
select 3,'001013','2011-5-1 0:00:00',450000.0000 union all
select 4,'001013','2011-4-1 0:00:00',100000.0000 union all
select 5,'001012','2011-4-1 0:00:00',10000500.0000 union all
select 6,'001014','2011-7-1 0:00:00',1200000.0000
create table #Forbug_HTMHkZje
(id int,hkdc nvarchar(10),hksj datetime,hkje dec(18,2))
insert #Forbug_HTMHkZje
select 1,'001014','2011-4-1 0:00:00',30000.0000 union all
select 2,'001014','2011-5-1 0:00:00',5000.0000 union all
select 3,'001013','2011-4-1 0:00:00',55555.0000
declare @str nvarchar(4000)
set @str='select xmsj as [time]'
select @str=@str+',max(case when xmdc='''+xmdc+''' then xmje else 0 end) as '''+xmdc+''''
from (select distinct xmdc from #Forbug_HTMZje) as a
set @str=@str+' from #Forbug_HTMZje group by xmsj'
declare @str2 nvarchar(4000)
set @str2='select hksj as [time]'
select @str2=@str2+',max(case when hkdc='''+hkdc+''' then hkje else 0 end) as '''+hkdc+''''
from (select distinct hkdc from #Forbug_HTMHkZje) as a
set @str2=@str2+' from #Forbug_HTMHkZje group by hksj'
declare @str3 nvarchar(4000)
set @str3='select * from ('+@str+') as a Left join ('+@str2+') as b on a.time=b.time'
exec(@str3)
--time 001012 001013 001014 time 001013 001014
------------------------- ------------- ------------- -------------- ----------------------- ----------------------------
--2011-04-01 00:00:00.000 10000500.00 100000.00 1000000.00 2011-04-01 00:00:00.000 55555.00 30000.00
--2011-05-01 00:00:00.000 0.00 450000.00 10000.00 2011-05-01 00:00:00.000 0.00 5000.00
--2011-07-01 00:00:00.000 0.00 0.00 1200000.00 NULL NULL NULL