--Trycreate table biao1(department Nvarchar(10),name Nvarchar(10),date datetime,money decimal(10,0))
insert into biao1 select N'销售',N'张三','2005-01-01',1
union all select N'销售',N'张三','2005-01-02',1
union all select N'销售',N'李四','2005-12-02',1
union all select N'供应',N'王五','2005-12-02',1
GO
Declare @I Int
Declare @S Nvarchar(4000)
Declare @I2 intSelect @S='',@I=1
While @I<=12
Begin
Select @I2=2005
while @I2>=2004
begin
Select @S=@S+', SUM(Case When Month([date])='+Rtrim(@I)+'and year([date])='+Rtrim(@I2)+' Then money Else 0 End) As ['+Right(@I2,2)+'年'+Right(100+@I,2)+N'月]'
select @I2=@I2-1
end
Select @I=@I+1
End
Select @S=N'Select IsNull(department,N''总计'') As department,(Case When Grouping(department)=1 Then '''' When Grouping(name)=1 Then N''合计'' Else name End ) As name'+@S+N',SUM([money]) As 总计 From biao1 Group By department,name with rollup'
EXEC(@S)
go
drop table biao1
insert into biao1 select N'销售',N'张三','2005-01-01',1
union all select N'销售',N'张三','2005-01-02',1
union all select N'销售',N'李四','2005-12-02',1
union all select N'供应',N'王五','2005-12-02',1
GO
Declare @I Int
Declare @S Nvarchar(4000)
Declare @I2 intSelect @S='',@I=1
While @I<=12
Begin
Select @I2=2005
while @I2>=2004
begin
Select @S=@S+', SUM(Case When Month([date])='+Rtrim(@I)+'and year([date])='+Rtrim(@I2)+' Then money Else 0 End) As ['+Right(@I2,2)+'年'+Right(100+@I,2)+N'月]'
select @I2=@I2-1
end
Select @I=@I+1
End
Select @S=N'Select IsNull(department,N''总计'') As department,(Case When Grouping(department)=1 Then '''' When Grouping(name)=1 Then N''合计'' Else name End ) As name'+@S+N',SUM([money]) As 总计 From biao1 Group By department,name with rollup'
EXEC(@S)
go
drop table biao1
解决方案 »
- 求助!SQL SERVER 登陆不上了
- 问像㎡这样的特殊字符怎么在SQL保存?另外要怎样才能正确显示出来?
- 请教:如何修改DTS包的Connection及Task
- 这个问题谁能给个准确的说法?谢谢了
- 如何实现每次的文件名称动态生成(用时间)?和按查询条件(动态时间,按天)导出呢,帮我啊,
- 创建出的表、视图的 OWNER 不是本用户
- 超简单问题,(在线等)!!!
- 一个csdn里面没有解决的问题(@@identity ), 高分求解,不够可以再加,谢谢!!!!!!
- 关于表的设计--象chinaren.com中班级管理员有批准的能力
- SQL数据更新
- 100高分求一条sql语句,急!
- 数据库同步问题,急!
运行结果非常令人开心,总计应将2004,2005分列。望能继续帮助解答。
Declare @I Int
Declare @S Nvarchar(4000)
Declare @I2 int
Declare @S1 Nvarchar(4000)Select @S='',@I=1
While @I<=12
Begin
Select @I2=2005
while @I2>=2004
begin
Select @S=@S+', SUM(Case When Month([date])='+Rtrim(@I)+'and year([date])='+Rtrim(@I2)+' Then money Else 0 End) As ['+Right(@I2,2)+'年'+Right(100+@I,2)+N'月]'
select @I2=@I2-1
end
Select @I=@I+1
End
Select @S=N'Select IsNull(department,N''总计'') As department,(Case When Grouping(department)=1 Then '''' When Grouping(name)=1 Then N''合计'' Else name End ) As name'+@S+N',SUM(case when year([date])=2005 then [money] else 0 end) As [2005总计],SUM(case when year([date])=2004 then [money] else 0 end) As [2004总计] From biao1 Group By department,name with rollup'
exec(@s)
create table biao1(department Nvarchar(10),name Nvarchar(10),date datetime,money decimal(10,0))
insert into biao1 select N'销售',N'张三','2005-01-01',1
union all select N'销售',N'张三','2005-01-02',1
union all select N'销售',N'李四','2005-12-02',1
union all select N'供应',N'王五','2005-12-02',1
GO
--建立存儲過程
Create ProceDure SP_Report(@Year Int)
As
Begin
Declare @I Int
Declare @S Nvarchar(4000)
Select @S='',@I=1
While @I<=12
Begin
Select @S=@S+', SUM(Case When Month([date])='+Rtrim(@I)+' And Year([date])='+Rtrim(@Year)+' Then money Else 0 End) As ['+Right(@Year,2)+'年'+Right(100+@I,2)+N'月]'
Select @S=@S+', SUM(Case When Month([date])='+Rtrim(@I)+' And Year([date])='+Rtrim(@Year-1)+' Then money Else 0 End) As ['+Right(@Year-1,2)+'年'+Right(100+@I,2)+N'月]'
Select @I=@I+1
End
Select @S=@S+N',SUM(Case When Year([date])='+Rtrim(@Year-1)+' Then money Else 0 End) As ['+Right(@Year-1,2)+N'年总计]'+N',SUM(Case When Year([date])='+Rtrim(@Year)+' Then money Else 0 End) As ['+Right(@Year,2)+N'年总计]'
Select @S=N'Select IsNull(department,N''总计'') As department,(Case When Grouping(department)=1 Then '''' When Grouping(name)=1 Then N''合计'' Else name End ) As name'+@S+' From biao1 Group By department,name With Rollup'
EXEC(@S)
End
GO
--測試
EXEC SP_Report 2005
GO
--刪除測試環境
Drop Table biao1
Drop ProceDure SP_Report
--結果
--省略