动态列!解决方法
日期：2014-05-17 浏览次数：20482 次
动态列!急!
下面的存储是我举例子的,并不正确!我想实现当我输入参数,2012，1，2012，12 的时候,Ytd就
得到那参数区间的和,问题就在,如果是同一年度,那么WHILE @beginm<=@endm就成立,
可如果我输,2011，4，2012，3，那就不成立了,那个字段名也不好拿了，望大家给点办法!谢谢!
下文的
isnull(Mtd201201,0.00) +
------解决方案--------------------
declare @beginy int ,
@beginm int ,
@endy int ,
@endm int
select @beginy=2012,@beginm=1,@endy=2012,@endm=12
DECLARE @sql NVARCHAR(MAX),@start DATETIME,@end DATETIME
SET @start=RTRIM(@beginy)+'-'+RTRIM(@beginm)+'-01'
SET @end=RTRIM(@endy)+'-'+RTRIM(@endm)+'-01'
DECLARE @i INT
WHILE @start<@end
BEGIN
SET @sql=ISNULL(@sql+'+'+CHAR(10),'')+'isnull([Mtd'+RTRIM(year(@start))+RTRIM(month(@start))+'],0.00)'
SET @start=DATEADD(mm,1,@start)
END
SET @sql='update dbo.RPT_resultA41_test set Ytd ='+@sql +' where CompanyID=1 and classify >0 and userName=''lop'''
PRINT @sql
------解决方案--------------------
declare @beginy int ,
@beginm int ,
@endy int ,
@endm int
select @beginy=2012,@beginm=1,@endy=2012,@endm=12
DECLARE @sql NVARCHAR(MAX),@start DATETIME,@end DATETIME
SET @start=RTRIM(@beginy)+'-'+RTRIM(@beginm)+'-01'
SET @end=RTRIM(@endy)+'-'+RTRIM(@endm)+'-01'
WHILE @start<=@end
BEGIN
SET @sql=ISNULL(@sql+'+'+CHAR(10),'')+'isnull([Mtd'+CONVERT(VARCHAR(6),@start,112)+'],0.00)'
SET @start=DATEADD(mm,1,@start)
END
SET @sql='update dbo.RPT_resultA41_test set Ytd ='+@sql +' where CompanyID=1 and classify >0 and userName=''lop'''
PRINT @sql
下面的存储是我举例子的,并不正确!我想实现当我输入参数,2012，1，2012，12 的时候,Ytd就
得到那参数区间的和,问题就在,如果是同一年度,那么WHILE @beginm<=@endm就成立,
可如果我输,2011，4，2012，3，那就不成立了,那个字段名也不好拿了，望大家给点办法!谢谢!
下文的
isnull(Mtd201201,0.00) +
用动态@total代替！
写得比较乱,大家看不懂的就在贴子了说明,谢谢!
create proc yyuu
(@beginy int ,
@beginm int ,
@endy int ,
@endm int
)
as
declare @total
WHILE @beginm<=@endm
BEGIN
SET @total=ISNULL(@total+'+','')+'[Mtd'+convert(varchar,@beginy)+RIGHT(100+@beginm,2)+']'
SET @beginm=@beginm+1
END
update dbo.RPT_resultA41_test
set Ytd =
isnull(Mtd201201,0.00) +
isnull(Mtd201202,0.00) +
isnull(Mtd201203,0.00) +
isnull(Mtd201204,0.00) +
isnull(Mtd201205,0.00) +
isnull(Mtd201206,0.00) +
isnull(Mtd201207,0.00) +
isnull(Mtd201208,0.00) +
isnull(Mtd201209,0.00) +
isnull(Mtd201210,0.00) +
isnull(Mtd201211,0.00) +
isnull(Mtd201212,0.00)
where CompanyID=1 and classify >0 and userName'lop'
------解决方案--------------------
declare @beginy int ,
@beginm int ,
@endy int ,
@endm int
select @beginy=2012,@beginm=1,@endy=2012,@endm=12
DECLARE @sql NVARCHAR(MAX),@start DATETIME,@end DATETIME
SET @start=RTRIM(@beginy)+'-'+RTRIM(@beginm)+'-01'
SET @end=RTRIM(@endy)+'-'+RTRIM(@endm)+'-01'
DECLARE @i INT
WHILE @start<@end
BEGIN
SET @sql=ISNULL(@sql+'+'+CHAR(10),'')+'isnull([Mtd'+RTRIM(year(@start))+RTRIM(month(@start))+'],0.00)'
SET @start=DATEADD(mm,1,@start)
END
SET @sql='update dbo.RPT_resultA41_test set Ytd ='+@sql +' where CompanyID=1 and classify >0 and userName=''lop'''
PRINT @sql
------解决方案--------------------
declare @beginy int ,
@beginm int ,
@endy int ,
@endm int
select @beginy=2012,@beginm=1,@endy=2012,@endm=12
DECLARE @sql NVARCHAR(MAX),@start DATETIME,@end DATETIME
SET @start=RTRIM(@beginy)+'-'+RTRIM(@beginm)+'-01'
SET @end=RTRIM(@endy)+'-'+RTRIM(@endm)+'-01'
WHILE @start<=@end
BEGIN
SET @sql=ISNULL(@sql+'+'+CHAR(10),'')+'isnull([Mtd'+CONVERT(VARCHAR(6),@start,112)+'],0.00)'
SET @start=DATEADD(mm,1,@start)
END
SET @sql='update dbo.RPT_resultA41_test set Ytd ='+@sql +' where CompanyID=1 and classify >0 and userName=''lop'''
PRINT @sql
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
