有一个表Table,其中有两个字段F1和F2。F2的当前值等于当前的F1值乘以上一条记录的F2值 --> 测试数据: [tb] if object_id('[tb]') is not null drop table [tb] create table [tb] (f1 int,f2 int) insert into [tb] select 5,6 union all select 3,2 union all select 5,7 go with wsp as (select px=row_number()over(order by getdate()),* from [tb]) select f1,f2,newf2=f1*isnull((select f2 from wsp where px=a.px-1),1) from wsp a--结果: f1 f2 newf2 ----------- ----------- ----------- 5 6 5 3 2 18 5 7 10
declare @t table (id int ,f1 int , f2 int) insert @t select 1,11,2 union all select 2,22,0 union all select 3,33,0 union all select 4,44,0 ;with tb as ( select id,f1,f2 from @t where id = 1 union all select b.id,b.f1,b.f1 * a.f2 as f2 from tb a inner join @t b on a.id = b.id - 1 ) select * from tb
我的原始表是这样的: declare @t table (f1 int , f2 int) insert @t select 11,NULL union all select 22,NULL union all select 33,NULL union all select 44,NULL
declare @t table (f1 int , f2 int) insert @t select 11,2 union all select 22,NULL union all select 33,NULL union all select 44,NULL;with tmp as ( select ROW_NUMBER() over (order by getdate()) as id,f1,f2 from @t ) ,tb as (
select id,f1,f2 from tmp where id = 1 union all select b.id,b.f1,b.f1 * a.f2 as f2 from tb a inner join tmp b on a.id = b.id - 1 ) select * from tb
--我的原始表是这样的: declare @t table (f1 int , f2 int) insert @t select 11,NULL union all select 22,NULL union all select 33,NULL union all select 44,NULL;with cte1 as ( select id=row_number()over(order by getdate()), * from @t ), cte2 as ( select a.id, a.f1, log(a.f1)+log(isnull(b.f2,1)) f2 from cte1 a left join cte1 b on a.id = b.id + 1 ) select id,f1, exp((select sum(f2) from cte2 where id<=t.id)) f2 from cte2 t/* id f1 f2 -------------------- ----------- ---------------------- 1 11 11 2 22 242 3 33 7986 4 44 351384 */
--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (f1 int,f2 int)
insert into [tb]
select 5,6 union all
select 3,2 union all
select 5,7
go
with wsp
as
(select px=row_number()over(order by getdate()),* from [tb])
select f1,f2,newf2=f1*isnull((select f2 from wsp where px=a.px-1),1) from wsp a--结果:
f1 f2 newf2
----------- ----------- -----------
5 6 5
3 2 18
5 7 10
insert @t
select 1,11,2
union all
select 2,22,0
union all
select 3,33,0
union all
select 4,44,0
;with tb as
(
select id,f1,f2 from @t where id = 1
union all
select b.id,b.f1,b.f1 * a.f2 as f2
from tb a
inner join @t b on a.id = b.id - 1
)
select * from tb
declare @t table (f1 int , f2 int)
insert @t
select 11,NULL
union all
select 22,NULL
union all
select 33,NULL
union all
select 44,NULL
declare @t table (f1 int , f2 int)
insert @t
select 11,2
union all
select 22,NULL
union all
select 33,NULL
union all
select 44,NULL;with tmp as
(
select ROW_NUMBER() over (order by getdate()) as id,f1,f2
from @t
)
,tb as
(
select id,f1,f2 from tmp where id = 1
union all
select b.id,b.f1,b.f1 * a.f2 as f2
from tb a
inner join tmp b on a.id = b.id - 1
)
select * from tb
declare @t table (f1 int , f2 int)
insert @t
select 11,NULL
union all
select 22,NULL
union all
select 33,NULL
union all
select 44,NULL;with cte1 as
(
select id=row_number()over(order by getdate()), * from @t
),
cte2 as
(
select
a.id, a.f1, log(a.f1)+log(isnull(b.f2,1)) f2
from
cte1 a left join cte1 b on a.id = b.id + 1
)
select id,f1, exp((select sum(f2) from cte2 where id<=t.id)) f2 from cte2 t/*
id f1 f2
-------------------- ----------- ----------------------
1 11 11
2 22 242
3 33 7986
4 44 351384
*/