if object_id('usp_Reort1') is not null
drop proc usp_Reort1
gocreate proc usp_Reort1
@Year int
as
begin
select @Year '年份',
[卡号],
[姓名],
( select sum1 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 1 )
group by [卡号], [姓名]) '1月',
( select sum2 from ( select [卡号], [姓名], sum([积分]) sum2 from t1
where year([时间]) = @Year and month([时间]) = 2 )
group by [卡号], [姓名]) '2月',
( select sum3 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 3 )
group by [卡号], [姓名]) '3月',
( select sum4 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 4 )
group by [卡号], [姓名]) '4月',
( select sum5 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 5 )
group by [卡号], [姓名]) '5月',
( select sum6 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 6 )
group by [卡号], [姓名]) '6月',
( select sum7 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 7 )
group by [卡号], [姓名]) '7月',
( select sum8 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 8 )
group by [卡号], [姓名]) '8月',
( select sum9 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 9 )
group by [卡号], [姓名]) '9月',
( select sum10 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 10 )
group by [卡号], [姓名]) '10月',
( select sum11 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 11 )
group by [卡号], [姓名]) '11月',
( select sum12 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 12 )
group by [卡号], [姓名]) '12月',
( select sum0 from ( select [卡号], [姓名], sum([积分]) sum0 from t1
where Year([时间]) = @Year
group by [卡号], [姓名]) '合计'
from t1
where year([时间]) = @Year union
select @Year, '合计', '',
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 1),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 2),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 3),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 4),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 5),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 6),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 7),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 8),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 9),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 10),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 11),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 12),
( select sum([积分] from t1
where Year([时间]) = @Year )
from t1
where year([时间]) = @Year
end
go最后执行 exec usp_Report1 2006
drop proc usp_Reort1
gocreate proc usp_Reort1
@Year int
as
begin
select @Year '年份',
[卡号],
[姓名],
( select sum1 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 1 )
group by [卡号], [姓名]) '1月',
( select sum2 from ( select [卡号], [姓名], sum([积分]) sum2 from t1
where year([时间]) = @Year and month([时间]) = 2 )
group by [卡号], [姓名]) '2月',
( select sum3 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 3 )
group by [卡号], [姓名]) '3月',
( select sum4 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 4 )
group by [卡号], [姓名]) '4月',
( select sum5 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 5 )
group by [卡号], [姓名]) '5月',
( select sum6 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 6 )
group by [卡号], [姓名]) '6月',
( select sum7 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 7 )
group by [卡号], [姓名]) '7月',
( select sum8 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 8 )
group by [卡号], [姓名]) '8月',
( select sum9 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 9 )
group by [卡号], [姓名]) '9月',
( select sum10 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 10 )
group by [卡号], [姓名]) '10月',
( select sum11 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 11 )
group by [卡号], [姓名]) '11月',
( select sum12 from ( select [卡号], [姓名], sum([积分]) sum1 from t1
where year([时间]) = @Year and month([时间]) = 12 )
group by [卡号], [姓名]) '12月',
( select sum0 from ( select [卡号], [姓名], sum([积分]) sum0 from t1
where Year([时间]) = @Year
group by [卡号], [姓名]) '合计'
from t1
where year([时间]) = @Year union
select @Year, '合计', '',
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 1),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 2),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 3),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 4),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 5),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 6),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 7),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 8),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 9),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 10),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 11),
( select sum([积分] from t1
where Year([时间]) = @Year and month([时间]) = 12),
( select sum([积分] from t1
where Year([时间]) = @Year )
from t1
where year([时间]) = @Year
end
go最后执行 exec usp_Report1 2006
set @sql=''
set @sql='Select 年份=Year(时间),卡号,姓名'
Select @sql= @sql + ',['+cast(Month(时间) as varchar)+'月'+']=sum(case Month(时间) when '+cast(Month(时间) as varchar)+' then 积分 else 0 end) '
from 表名 group by Month(时间) order by Month(时间)
Select @sql= @sql+' from 表名 group by 姓名,卡号,Year(时间) order by Year(时间)'
exec(@sql)
declare @sql varchar(4000)
set @sql='select 年份=Year(salesDate), CASE WHEN (GROUPING(VipCode) = 1) THEN ''合计'' ELSE VipCode end as 卡号'
select @sql=@sql+','''+convert(varchar,DATEPART(mm,[salesDate]))+'月''=isnull(sum(case convert(varchar,DATEPART(mm,[salesDate]))
when '''+convert(varchar,DATEPART(mm,[salesDate]))+''' then sinte end),0)'
from CRMVipinte
group by DATEPART(mm,[salesDate]),salesDate,vipCode order by DATEPART(mm,[salesDate])
set @sql=@sql+',''合计''=sum(Sinte) from CRMVipinte group by Year(salesDate),VipCode with rollup'
exec(@sql)
THEN '合计' ELSE VipCode end as 卡号,
'1月'=isnull(sum(case convert (varchar,Month([salesDate])) when '1' then sinte end),0),
'2月'=isnull(sum(case convert (varchar,Month([salesDate])) when '2' then sinte end),0),
'3月'=isnull(sum(case convert (varchar,Month([salesDate])) when '3' then sinte end),0),
'4月'=isnull(sum(case convert (varchar,Month([salesDate])) when '4' then sinte end),0),
'5月'=isnull(sum(case convert (varchar,Month([salesDate])) when '5' then sinte end),0),
'6月'=isnull(sum(case convert (varchar,Month([salesDate])) when '6' then sinte end),0),
'7月'=isnull(sum(case convert (varchar,Month([salesDate])) when '7' then sinte end),0),
'8月'=isnull(sum(case convert (varchar,Month([salesDate])) when '8' then sinte end),0),
'9月'=isnull(sum(case convert (varchar,Month([salesDate])) when '9' then sinte end),0),
'10月'=isnull(sum(case convert (varchar,Month([salesDate])) when '10' then sinte end),0),
'11月'=isnull(sum(case convert (varchar,Month([salesDate])) when '11' then sinte end),0),
'12月'=isnull(sum(case convert (varchar,Month([salesDate])) when '12' then sinte end),0),
'合计'=sum(Sinte) from CRMVipinte group by Year(salesDate),VipCode with rollup 还不能按年分 请高手帮看一下。谢谢!
另外不知道我写的存储过程有什么不对的地方,请LZ指教