现有表[Hong_Props],表中的字段如下：
PropID PropGameType PropArrea type 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
先我要得到的查询效果为：
日期 道具A 道具B 道具C 道具D 道具E 累计
2012-02-11 24 15 0 0 0 39
2012-02-12 0 0 14 2 0 16
2012-02-13 0 0 0 50 0 50
2012-02-14 0 9 0 0 10 19
2012-02-15 20 0 0 0 10 30
急求高手帮忙！
急求高手帮忙！
急求高手帮忙！
急求高手帮忙！
------解决方案--------------------
- SQL code
--PropTime Props PropsCoundeclare @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)) tselect @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
------解决方案--------------------
- 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' ,20go 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 ) tselect @str = @str + ' from Hong_Props group by PropTime'exec(@str)PropTime 道具A 道具B 道具C 道具D 道具E---------- ----------- ----------- ----------- ----------- -----------2012-02-11 24 15 0 0 02012-02-12 0 0 14 2 02012-02-13 0 0 0 50 02012-02-14 0 9 0 0 02012-02-15 20 0 0 0 10(5 行受影响)
------解决方案--------------------
------解决方案--------------------
- SQL code
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 ) tselect @str = @str +',SUM(PropsCoun) as 累计'+ ' from Hong_Props group by PropTime'print (@str)exec(@str)PropTime 道具A 道具B 道具C 道具D 道具E 累计---------- ----------- ----------- ----------- ----------- ----------- -----------2012-02-11 24 15 0 0 0 392012-02-12 0 0 14 2 0 162012-02-13 0 0 0 50 0 502012-02-14 0 9 0 0 0 92012-02-15 20 0 0 0 10 30(5 行受影响)---orselect convert(varchar(10),PropTime,120) PropTime,sum(case when Props='道具A' then PropsCoun else 0 end) [道具A],sum(case when Props='道具B' then PropsCoun else 0 end) [道具B],sum(case when Props='道具C' then PropsCoun else 0 end) [道具C],sum(case when Props='道具D' then PropsCoun else 0 end) [道具D],sum(case when Props='道具E' then PropsCoun else 0 end) [道具E],SUM(PropsCoun) as 累计 from Hong_Props group by PropTimePropTime 道具A 道具B 道具C 道具D 道具E 累计---------- ----------- ----------- ----------- ----------- ----------- -----------2012-02-11 24 15 0 0 0 392012-02-12 0 0 14 2 0 162012-02-13 0 0 0 50 0 502012-02-14 0 9 0 0 0 92012-02-15 20 0 0 0 10 30(5 行受影响)