27,579
社区成员
发帖
与我相关
我的任务
分享
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 +',SUM(PropsCoun) as 累计'+ ' from Hong_Props group by PropTime'
print (@str)
exec(@str)
select 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 PropTime
PropTime 道具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 0 9
2012-02-15 20 0 0 0 10 30
(5 行受影响)
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 +',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 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 0 9
2012-02-15 20 0 0 0 10 30
(5 行受影响)
---or
select 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 PropTime
PropTime 道具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 0 9
2012-02-15 20 0 0 0 10 30
(5 行受影响)
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 行受影响)
--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)