取最大公因數
Create FUNCTION dbo.GCD(@a INT, @b INT)RETURNS INT
AS
BEGIN
--最大公因數
WHILE @b != 0
BEGIN
DECLARE @temp INT;
SET @temp = @b;
SET @b = @a % @b;
SET @a = @temp;
END
RETURN @a;
END;
十進位轉分數
Create function dbo.DecimalToFraction (@decimal_value DECIMAL(18, 2))
returns NVARCHAR(50) AS
BEGIN
DECLARE @whole_part INT;
DECLARE @fraction_part DECIMAL(18, 2);
DECLARE @numerator INT;
DECLARE @denominator INT;
DECLARE @gcd_value INT;
DECLARE @fraction_string NVARCHAR(50);
-- 取得整數部分和小數部分
SET @whole_part = FLOOR(@decimal_value);
SET @fraction_part = @decimal_value - @whole_part;
-- 初始分母为100，以处理小数部分的十分位
SET @denominator = 100;
-- 将小数部分转换为分数的分子 0.75 * 100
SET @numerator = @fraction_part * @denominator;
-- 确保分子和分母为整数
SET @numerator = ROUND(@numerator, 0);
SET @denominator = ROUND(@denominator, 0);
-- 求分子和分母的最大公因数
SET @gcd_value = dbo.GCD(@numerator, @denominator);
WHILE @gcd_value > 1
BEGIN
SET @numerator = @numerator / @gcd_value;
SET @denominator = @denominator / @gcd_value;
SET @gcd_value = dbo.GCD(@numerator, @denominator);
END;
-- 将整数部分和分数部分组合成分数形式
if @numerator<>0
begin
SET @fraction_string = CONCAT(@whole_part, '-', @numerator, '/', @denominator);
end
else
begin
SET @fraction_string = @whole_part
end
return @fraction_string;
END;