求助:使用T-SQL编写S=1!+3!+5!+7!+……+N! 直到10000时,输出N和S的值.

declare @s as int
declare @k as int
declare @n as int
declare @m as int
declare @l as int
set @s = 0
set @k = 0
set @n = 1
set @m = 1
set @l = 1
while @s <= 10000
begin
  while @m <= @n
  begin
    set @k = @k + @m 
    set @m = @m + 1
  end
  set @s = @s + @k  
  set @l = @l + 1
  set @n = @l*2 + 1
  set @k = 0
  set @m = 1
end

print 'N = ' + cast((@n - 1)/2 as varchar)
print 'S = ' + cast(@s as varchar)

/*
N = 25
S = 10719
*/

declare @s as int
declare @k as int
declare @n as int
declare @m as int
declare @l as int
set @s = 0
set @k = 0
set @n = 1
set @m = 1
set @l = 1
while @s <= 10000
begin
  while @m <= @n
  begin
    if @m = 1 
       set @k = @k + @m 
    else
       set @k = @k * @m
    set @m = @m + 1
  end
  set @s = @s + @k  
  set @l = @l + 1
  set @n = @l*2 + 1
  set @k = 0
  set @m = 1
end

print 'N = ' + cast((@n - 1)/2 as varchar)
print 'S = ' + cast(@s as varchar)

/*
N = 5
S = 368041
*/

create function fn_factorial 
(
    @n int 
)
returns bigint
as 
begin
    declare @i int,@result bigint 
    set @i=1 set @result=1 
    while @i<=@n 
    begin 
    set @result=@result*@i 
    set @i=@i+1 
    end 
    return @result 
end

declare @i int set @i=1
declare @j int set @j=1
declare @sum bigint set @sum=0

while 1=1
begin
    set @sum=@sum+dbo.fn_factorial(@i)
    if(@sum>10000) break 
    set @j=@j+1
    set @i=2*@j-1
end
--S值和大乌龟计算的不同
select @i as N,@sum as S
/*
N           S
----------- --------------------
9           368047
*/

;with cte as(
select 1 as n,1 as f,1 as s
union all
select n+2,f*(n+1)*(n+2),s+f*(n+1)*(n+2) from cte where s<10000
)select top 1 n,s from cte order by n desc
/*
n           s
----------- -----------
9           368047

(1 行受影响)

*/

declare @n int,@s int
set @s=1
select @n=(case when @s<10000 then 2*number+1 else @n end),
@s=(case when @s<10000 then @s+@s*2*number*(2*number+1) else @s end)
from master..spt_values 
where type='p'
select @n n,@s s
/*
n           s
----------- -----------
9           368047

(1 行受影响)

*/

declare @n int,@s int,@i int
set @i=1
set @s=1
select @n=(case when @s<10000 then 2*number+1 else @n end),
@s=(case when @s<10000 then @s+@i*2*number*(2*number+1) else @s end),
@i=(case when @s<10000 then @i*2*number*(2*number+1) else @i end)
from master..spt_values 
where type='p' and number>0
select @n n,@s s
/*
n           s
----------- -----------
9           368047

(1 行受影响)

*/