SQL Server善用LOG10 和POWER 函數

来源：互联网发布：天金加银软件最新版本编辑：程序博客网时间：2024/05/08 22:05

MSDN聯機註解：LOG10 和POWER 函數是彼此反向關聯。例如，10^ LOG10(n) = n。只有一個值的轉換時沒問題，以下測測結果集。;WITH CAS(SELECT number FROM master..spt_values WHERE number BETWEEN 1 AND 10 AND type IN('P'))SELECT POWER(10,SUM(LOG10(number))) FROM C/*3628800*/--用一個變量驗證一下DECLARE @i INTSET @i=1;WITH CAS(SELECT number FROM master..spt_values WHERE number BETWEEN 1 AND 10 AND type IN('P'))SELECT @i=@i*number FROM CSELECT @i/*3628800*/--相同正確go--改變一下測試數據DECLARE @T TABLE(number int)INSERT @Tselect 1 UNION ALL SELECT 2 UNION ALLSELECT 4SELECT POWER(10,SUM(LOG10(number))) FROM @T/*7*/DECLARE @i INTSET @i=1SELECT @i=@i*number FROM @TSELECT @i/*8*/--結果不正確了反而用自然對數的基底時，更準確。SELECT EXP(SUM(LOG(number))) FROM @T  /*8*/結論：在結果集乘積時，別用Log10對數，還是自定議函數穩定。