[Challenge] 階乘

Beyond Relational TSQL Beginners Challenge 3

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

Nums        Factorial
----------- -----------
0                    1
1                    1
3                    6
5                  120
10             3628800

• Rules
1. Nums should be sorted in Ascending Order.
2. The program should run in SQL SERVER 2005 and above.
3. The output should be in the same way as it has been shown. Column names should be exactly the same and the result must be sorted in Ascending order of Nums.
4. The program has to be done by a single query and should begin either with a SELECT or WITH statement with no variables, temporary table, table variables permitted.
5. You cannot use RBAR, cursors, loops etc. in your program.

• 個人解法

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact 
SELECT 0 UNION ALL 
SELECT 1 UNION ALL 
SELECT 3 UNION ALL 
SELECT 5 UNION ALL 
SELECT 10

; 
WITH CTE AS
(
  SELECT Nums , 1 AS Factorial ,1 AS Start 
  FROM @Fact
  UNION ALL
  SELECT Nums , Factorial * (Start + 1) ,  (Start + 1)
  FROM CTE
  WHERE Start < Nums
)
SELECT Nums , MAX(Factorial) AS Factorial
FROM CTE
GROUP BY Nums
ORDER BY Nums

希望結果是 Factorial 數字必須整個靠右，原是想利用 REPLICATE() 搭配 SPACE(1) 讓結果靠右，但這個作法都對不齊，查看每個 Solutions，也沒有發現讓 Factorial 靠右的寫法。

參考資料
• TSQL Beginners Challenge 3 - Find the Factorial
• SQL SERVER – Right Aligning Numerics in SQL Server Management Studio (SSMS) - 以文字顯示結果，在 SSMS 上有選項可以設定，讓數值資料向右對齊