日期:2014-05-18 浏览次数:20641 次
--PropTime Props PropsCoun declare @sql varchar(8000) set @sql = 'select convert(varchar(10),PropTime,120) PropTime' select @sql = @sql + ',sum(case when convert(varchar(10),PropTime,120)='''+date+''' then PropsCoun else 0 end) ['+date+']' from(select convert(varchar(10),PropTime,120) date from Hong_Props group by convert(varchar(10),PropTime,120)) t select @sql = @sql + ' from Hong_Props group by convert(varchar(10),PropTime,120)' exec(@sql)
------解决方案--------------------
好像做过类是的帖子
、
http://topic.csdn.net/u/20120217/11/cb980785-7fe5-4e64-a939-312759193221.html
------解决方案--------------------
create table Hong_Props( PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int ) insert into Hong_Props select 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union all select 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union all select 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union all select 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union all select 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union all select 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union all select 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union all select 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20 go declare @str varchar(max) set @str = 'select convert(varchar(10),PropTime,120) PropTime' select @str = @str + ',sum(case when Props='''+Props+''' then PropsCoun else 0 end) ['+Props+']' from(select Props from Hong_Props group by Props ) t select @str = @str + ' from Hong_Props group by PropTime' exec(@str) PropTime 道具A 道具B 道具C 道具D 道具E ---------- ----------- ----------- ----------- ----------- ----------- 2012-02-11 24 15 0 0 0 2012-02-12 0 0 14 2 0 2012-02-13 0 0 0 50 0 2012-02-14 0 9 0 0 0 2012-02-15 20 0 0 0 10 (5 行受影响)
------解决方案--------------------
declare @str varchar(max) set @str = 'select convert(varchar(10),PropTime,120) PropTime' select @str = @str + ',sum(c