SQL Server 将小数转换成分数的方法 dbo.cusf_DecimalToFraction(@DecimalValue DECIMAL(18, 10))

 

USE TESTDB
GO

-- =============================================
-- Author:      WRN
-- Create date: 2024-11-25 16:30:34
-- Database:    TESTDB
-- Description:   将小数转成分数形式
-- =============================================



ALTER FUNCTION dbo.CusF_DecimalToFraction (@DecimalValue DECIMAL(18, 10))
RETURNS VARCHAR(50)
AS
BEGIN
  DECLARE @numerator INT;
  DECLARE @denominator INT;
  DECLARE @gcd INT;
  DECLARE @tempNumerator INT;
  DECLARE @tempDenominator INT;
  DECLARE @fraction VARCHAR(50);

  -- 假设我们有一个简单的分数化算法（这里只是一个示例）
  SET @numerator = @DecimalValue * 1000000; -- 乘以一个大数以避免浮点误差
  SET @denominator = 1000000;

  -- 计算最大公约数（GCD）
  SET @tempNumerator = @numerator;
  SET @tempDenominator = @denominator;
  WHILE @tempDenominator <> 0
  BEGIN
  SET @gcd = @tempDenominator;
  SET @tempDenominator = @tempNumerator % @tempDenominator;
  SET @tempNumerator = @gcd;
  END;

  -- 约分分数
  SET @numerator = @numerator / @gcd;
  SET @denominator = @denominator / @gcd;

  -- 构建分数形式的字符串
  SET @fraction = CAST(@numerator AS VARCHAR) + '/' + CAST(@denominator AS VARCHAR);

  RETURN @fraction;
END;

-- 使用函数 调用此方法即可
 -- SELECT dbo.DecimalToFraction(0.75) AS Fraction;
GO

调用方式及结果：