SQL 十進位轉分數

取最大公因數 

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;