TSQL Beginners Challenge 3

这是一个关于CTE的应用，这里我们用CTE实现阶乘 Factorial，首先来看一个简单的小实验，然后再来看题目。有的童鞋会问怎么没有2就来3了呢，惭愧，TSQL Beginners Challenge 2对应的题目我没能做出来。上代码：

;with cte as 
(
select num=1,fact=1 union all 
select num=num+1,fact=fact*(num+1) from cte  where num<5
)
select * from cte

上面的查询会有什么结果呢，大家可以粘到查询分析器里面看下，简单的实现了阶乘吧。CTE的递归是有层级限制的，写Blog的时候想不起来相关的语法结构，又懒得去查，偷个懒直接用num<5来限制下，以免报错。：）。有了这个打的，下面的题目就很容易了，现在我们来看题目吧：

Introduction

This challenge though does not have any resemblance with the real time problem directly, but it measures about logical thinking. The problem is all about finding the factorial of numbers. Though it is known to most of us what a factorial is, but to recall the concept here is an example:

Factorial of 3 is 1*2*3 = 6 i.e. the factorial of a non-negative integer n, denoted by n!, is the product of all positive integers less than or equal to n.

Sample Data

Nums
-----------
0 
1 
3 
5 
10 

Expected Results

1.Nums        Factorial
2.----------- -----------
3.0                    1
4.1                    1
5.3                    6
6.5                  120
7.10             3628800

--创建表
CREATE TABLE [Fact](
    [Nums] [int] NULL
) 

--构造数据
insert into Fact(Nums) values(0)
insert into Fact(Nums) values(1)
insert into Fact(Nums) values(10)
insert into Fact(Nums) values(3)
insert into Fact(Nums) values(5)

--方式一
;WITH cte AS 
(
SELECT num=0,factorial=1
UNION ALL 
SELECT num=num+1,(num+1)*factorial FROM cte WHERE num<10
)
SELECT Nums,factorial FROM cte a, Fact b WHERE a.num=b.Nums
 