现有表[Hong_Props],表中的字段如下：
PropID PropGameType PropArrea PropTime Props PropsCoun
1 1 1 2012-02-11 道具A 24
2 2 2 2012-02-11 道具B 15
3 1 1 2012-02-12 道具C 14
4 2 1 2012-02-12 道具D 2
5 2 2 2012-02-13 道具D 50
6 1 2 2012-02-14 道具B 9
7 2 2 2012-02-15 道具E 10
8 1 1 2012-02-15 道具A 20
先我要得到的查询效果为：
日期 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 累计
道具A 24 / / / 20 44
道具B 15 / / 9 / 24
道具C / 14 / / / 14
道具D / 2 50 / / 52
道具E / / / / 10 10
急求高手帮忙！
急求高手帮忙！
急求高手帮忙！
急求高手帮忙！
------解决方案--------------------
- SQL code
declare @sql varchar(max)set @sql = 'select Props'select @sql = @sql + ',sum(case convert(varchar(8),PropTime,112) when '''+date+''' then PropsCoun else 0 end) ['+date+']'from( select convert(varchar(8),PropTime,112) date from Hong_Props group by convert(varchar(8),PropTime,112)) tselect @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props 'exec(@sql)
------解决方案--------------------
- SQL code
create table Hong_Props(PropID int,PropGameType int,PropArrea int,PropTime datetime,Props varchar(20),PropsCoun int)insert into Hong_Propsselect 1 ,1 ,1 ,'2012-02-11' ,'道具A' ,24 union allselect 2 ,2 ,2 ,'2012-02-11' ,'道具B' ,15 union allselect 3 ,1 ,1 ,'2012-02-12' ,'道具C' ,14 union allselect 4 ,2 ,1 ,'2012-02-12' ,'道具D' ,2 union allselect 5 ,2 ,2 ,'2012-02-13' ,'道具D' ,50 union allselect 6 ,1 ,2 ,'2012-02-14' ,'道具B' ,9 union allselect 7 ,2 ,2 ,'2012-02-15' ,'道具E' ,10 union allselect 8 ,1 ,1 ,'2012-02-15' ,'道具A' ,20godeclare @sql varchar(max)set @sql = 'select Props'select @sql = @sql + ',sum(case convert(varchar(10),PropTime,120) when '''+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)) torder by dateselect @sql = @sql + ',sum(PropsCoun) as [累计] from Hong_Props group by Props 'exec(@sql)drop table Hong_Props/*************************Props 2012-02-11 2012-02-12 2012-02-13 2012-02-14 2012-02-15 累计-------------------- ----------- ----------- ----------- ----------- ----------- -----------道具A 24 0 0 0 20 44道具B 15 0 0 9 0 24道具C 0 14 0 0 0 14道具D 0 2 50 0 0 52道具E 0 0 0 0 10 10(5 行受影响)
------解决方案--------------------
- SQL code
--如果PropTime字段为字符串型declare @sql varchar(8000)set @sql = 'select Props 'select @sql = @sql + ' , max(case PropTime when ''' + PropTime + ''' then PropsCoun else 0 end) [' + PropTime + ']'from (select distinct PropTime from Hong_Props) as aset @sql = @sql + ' ,sum(PropsCoun) 累计 from Hong_Props group by Props'exec(@sql) --如果PropTime字段为时间型declare @sql varchar(8000)set @sql = 'select Props 'select @sql = @sql + ' , max(case PropTime when ''' + PropTime + ''' then PropsCoun else 0 end) [' + PropTime + ']'from (select distinct convert(varchar(10),PropTime,120) PropTime from Hong_Props) as aset @sql = @sql + ' ,sum(PropsCoun) 累计 from (select convert(varchar(10),PropTime,120) PropTime, Props ,PropsCoun from Hong_Props) t group by Props'exec(@sql)