如何计算?

[kuangdp]

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


最后我要得到 按年分的ＶＡＬＵＥ的连乘值-1然后去除该年份中包含的月数　,解决就结!
如 2003 :(11*12*13-1)/3

[kuangdp]

我那些数据计算后存放在临时表里的,不能调用函数,忘记说了
还有什么方法吗?

[pt1314917]

2楼没有用函数。怎么不行么？

[kuangdp]

背着灵魂漫步,你的是可以算出值,但是我有很多个人,每个人都需要算出每年的一个这个指标数,然后要更新到另一个表里去,而且都是在过程里用临时表计算的
我后来自己写了一下,用双重游标来计算每人每年的这个指标数了(用变量来保存每年的每月的值,然后连乘的)
结贴!谢谢背着灵魂漫步,树上的鸟儿~

[birdie_mas]

create table tempyear(name varchar(10),date varchar(10),value int)
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
go

select distinct name, substring(date, 1, 4), sum(value*3) - 3 value from tempyear
group by name , substring(date, 1, 4)
go

[birdie_mas]

寫錯了
是乘,不是加

[birdie_mas]

只能寫函數,沒有辦法

[kuangdp]

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', 11

go

--如果是物理表##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 int

DECLARE @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	

END

CLOSE P1_CURSOR
DEALLOCATE P1_CURSOR	

贴出来分享一下~

[JiangHongTao]

结贴吧，把分给我。
完全基于表变量，临时表一样使用，没有函数和存储过程。语句简短易懂。
有测试结果。

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',11

declare @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
*/

[JiangHongTao]

多人也支持，不给分给我都不行了。

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',18

declare @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
*/

[kuangdp]

果然不错啊~~

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

这段是什么原理?

[JiangHongTao]

建议LZ加分结贴。

[yangjiexi]

先帮顶,再看看!!

[pt1314917]

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')

[chuifengde]

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', 11

go
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 行）

*/

[kuangdp]

呵呵~分是次要的,共同学习,共同进步才是根本嘛~~~~好了~~结帖!