name date value
zhangsan 200310 11
zhangsan 200311 12
zhangsan 200312 13
zhangsan 200401 14
zhangsan 200402 15
zhangsan 200403 16
zhangsan 200404 17
zhangsan 200405 18
zhangsan 200408 11
zhangsan 200409 11
最后我要得到 按年分的VALUE的连乘值-1然后去除该年份中包含的月数 ,解决就结!
如 2003 :(11*12*13-1)/3
zhangsan 200310 11
zhangsan 200311 12
zhangsan 200312 13
zhangsan 200401 14
zhangsan 200402 15
zhangsan 200403 16
zhangsan 200404 17
zhangsan 200405 18
zhangsan 200408 11
zhangsan 200409 11
最后我要得到 按年分的VALUE的连乘值-1然后去除该年份中包含的月数 ,解决就结!
如 2003 :(11*12*13-1)/3
create table du(name varchar(10),date varchar(10),value int)
insert into du select 'zhangsan','200310',11
insert into du select 'zhangsan','200311',12
insert into du select 'zhangsan','200312',13
insert into du select 'zhangsan','200401',14
insert into du select 'zhangsan','200402',15
insert into du select 'zhangsan','200403',16
insert into du select 'zhangsan','200404',17
insert into du select 'zhangsan','200405',18
insert into du select 'zhangsan','200408',11
insert into du select 'zhangsan','200409',11
declare @sql varchar(1000)
select @sql=isnull(@sql+'*','')+cast(value as varchar) from du where left(date,4)='2003'
exec('select ('+ @sql +'-1)*1.0/3')
insert a select 'zhangsan','200310', 11
union all select 'zhangsan','200311', 12
union all select 'zhangsan','200312', 13
union all select 'zhangsan','200401', 14
union all select 'zhangsan','200402', 15
union all select 'zhangsan','200403', 16
union all select 'zhangsan','200404', 17
union all select 'zhangsan','200405', 18
union all select 'zhangsan','200408', 11
union all select 'zhangsan','200409', 11go
create function getVa(@date char(4))
returns decimal(20,4)
as
begin
declare @s decimal(20,4)
set @s=1
select @s=@s*value from a where left(date,4)=@date
return @s-1
end
go
select name,d date,convert(decimal(20,4),dbo.getVa(d)/v) value
from(
select
name,left(date,4)d ,count(1) v
from a
group by name,left(date,4)
)aa--result
/*name date value
-------------------- ------ ----------------------
zhangsan 2003 571.6667
zhangsan 2004 17772479.8571(所影响的行数为 2 行)*/
go
insert into tempyear select 'zhangsan','200310',11
insert into tempyear select 'zhangsan','200311',12
insert into tempyear select 'zhangsan','200312',13
insert into tempyear select 'zhangsan','200401',14
insert into tempyear select 'zhangsan','200402',15
insert into tempyear select 'zhangsan','200403',16
insert into tempyear select 'zhangsan','200404',17
insert into tempyear select 'zhangsan','200405',18
insert into tempyear select 'zhangsan','200408',11
insert into tempyear select 'zhangsan','200409',11
goselect distinct name, substring(date, 1, 4), sum(value*3) - 3 value from tempyear
group by name , substring(date, 1, 4)
go
create table ##a(name varchar(20),date varchar(6),value int)
insert ##a select 'zhangsan','200310', 11
union all select 'zhangsan','200311', 12
union all select 'zhangsan','200312', 13
union all select 'zhangsan','200401', 14
union all select 'zhangsan','200402', 15
union all select 'zhangsan','200403', 16
union all select 'zhangsan','200404', 17
union all select 'zhangsan','200405', 18
union all select 'zhangsan','200408', 11
union all select 'zhangsan','200409', 11go--如果是物理表##a,可用以下方法
create function getVa(@date char(4))
returns decimal(20,4)
as
begin
declare @s decimal(20,4)
set @s=1
select @s=@s*value from ##a where left(date,4)=@date
return @s-1
end
go
select name,d date,convert(decimal(20,4),dbo.getVa(d)/v) value
from(
select
name,left(date,4)d ,count(1) v
from ##a
group by name,left(date,4)
)aa--如果是临时表##a,可用以下方法
DROP TABLE ##TEMP1 Create table ##TEMP1(date varchar(4),value numeric(18,4))
Declare @p1 numeric(18,4),
@p2 numeric(18,4),
@i intDECLARE @F0010 varchar(4) ,
@date varchar(6) DECLARE P1_CURSOR CURSOR FOR
SELECT distinct left(date,4) FROM ##a
OPEN P1_CURSOR
FETCH NEXT FROM P1_CURSOR INTO @F0010 --年份
WHILE @@FETCH_STATUS = 0
BEGIN
select @p2=1,@i=0
DECLARE P2_CURSOR CURSOR FOR
SELECT date FROM ##a where left(date,4)=@F0010
OPEN P2_CURSOR
FETCH NEXT FROM P2_CURSOR INTO @date
WHILE @@FETCH_STATUS = 0
BEGIN
--月个数
select @p1=null
select @p1=value from ##a where date=@date
select @p2=@p2*@p1
set @i=@i+1
FETCH NEXT FROM P2_CURSOR INTO @date
END
INSERT INTO ##TEMP1
SELECT @F0010,(@P2-1)/@I CLOSE P2_CURSOR
DEALLOCATE P2_CURSOR
FETCH NEXT FROM P1_CURSOR INTO @F0010 ENDCLOSE P1_CURSOR
DEALLOCATE P1_CURSOR 贴出来分享一下~
完全基于表变量,临时表一样使用,没有函数和存储过程。语句简短易懂。
有测试结果。
declare @t table(name varchar(10),date varchar(10),value int)
declare @t1 table(date varchar(10),value int,s int,c int)
insert @t select 'zhangsan','200310',11
union select 'zhangsan','200311',12
union select 'zhangsan','200312',13
union select 'zhangsan','200401',14
union select 'zhangsan','200402',15
union select 'zhangsan','200403',16
union select 'zhangsan','200404',17
union select 'zhangsan','200405',18
union select 'zhangsan','200408',11
union select 'zhangsan','200409',11declare @d varchar(10),@v int
select @d = ''
insert @t1 select left(date,4),value,0,
(select count(*) from @t where left(date,4) = left(a.date,4)) from @t a
update @t1 set s = case when @d = date then @v else value end,
@v = case when @d = date then @v*value else value end,
@d = date
select date,max((s -1)/c) v from @t1 group by date order by date
/*
date v
---------- -----------
2003 571
2004 17772479
*/
declare @t table(name varchar(10),date varchar(10),value int)
declare @t1 table(name varchar(10),date varchar(10),value int,s int,c int)
insert @t select 'zhangsan','200310',11
union select 'zhangsan','200311',12
union select 'zhangsan','200312',13
union select 'zhangsan','200401',14
union select 'zhangsan','200402',15
union select 'zhangsan','200403',16
union select 'zhangsan','200404',17
union select 'zhangsan','200405',18
union select 'zhangsan','200408',11
union select 'lisi','200308',11
union select 'lisi','200309',15
union select 'lisi','200407',17
union select 'lisi','200409',18declare @d varchar(10),@v int
select @d = ''
insert @t1 select name,left(date,4),value,0,
(select count(*) from @t where left(date,4) = left(a.date,4) and name = a.name) from @t a
update @t1 set s = case when @d = date then @v else value end,
@v = case when @d = date then @v*value else value end,
@d = date
select name,date,max((s -1)/c) v from @t1 group by name,date order by name,date
/*
name date v
---------- ---------- -----------
lisi 2003 82
lisi 2004 152
zhangsan 2003 571
zhangsan 2004 1884959
*/
@v = case when @d = date then @v*value else value end,
@d = date这段是什么原理?