如何添加分数在SQL Server?

Question

我正在寻找一种方法来添加存储为1/3,2/3或1/6等分数

输出应该以分数表示，只有1/4 + 2/4 = 3/4或者1/4 + 1/4 + 1/4 + 1/4 = 1

Answer 1

在不知道数据类型和表结构的情况下，我假设分数存储在VARCHAR字段中。下面是一个简短的脚本，展示了如何添加存储在VARCHAR字段中的两个分数。该过程是将分数解析为单独的分子和分母，通过找到两个分母的乘积的公分母进行蛮力加法，然后使用得到的分子和分母的欧几里得算法(https://en.wikipedia.org/wiki/Euclidean_algorithm)找到最大公约数(GCD)来减少分数，然后每个除以GCD。这个解释可能比只看样例脚本更糟糕。

DECLARE @f1 VARCHAR(5) = '1/4';
DECLARE @f2 VARCHAR(5) = '1/2';
-- declare variables for the numerators and denominators
DECLARE @n1 INT, @n2 INT, @d1 INT, @d2 INT, @resultN INT, @resultD INT;
-- find the individual numerators
SELECT @n1 = CAST(SUBSTRING(@f1, 1, CHARINDEX('/', @f1, 1) - 1) AS INT);
SELECT @n2 = CAST(SUBSTRING(@f2, 1, CHARINDEX('/', @f2, 1) - 1) AS INT);
-- find the individual denominators
SELECT @d1 = CAST(SUBSTRING(@f1, CHARINDEX('/', @f1, 1) + 1, LEN(@f1) - CHARINDEX('/', @f1, 1)) AS INT);
SELECT @d2 = CAST(SUBSTRING(@f2, CHARINDEX('/', @f2, 1) + 1, LEN(@f2) - CHARINDEX('/', @f2, 1)) AS INT);
-- a common denominator is the product of the two denominators
SELECT @resultD = @d1 * @d2;
-- the numerator is the sum of the numerators multiplied by the other denominator
SELECT @resultN = @n1 * @d2 + @n2 * @d1;
-- here is the sum without reducing the fraction
SELECT CONCAT(@resultN, '/', @resultD) AS [Unreduced Result];
-- to reduce, find the GCD of the @resultN and @resultD and divide each by the GCD
DECLARE @a INT, @b INT, @t INT;
SELECT @a = ABS(@resultN);
SELECT @b = ABS(@resultD);
WHILE (@b <> 0) 
BEGIN 
SELECT @t = @b;
SELECT @b = @a % @b;
SELECT @a = @t;
END
-- at this point @a is the GCD
SELECT @resultN = @resultN / @a;
SELECT @resultD = @resultD / @a;
SELECT CONCAT(@resultN, '/', @resultD) AS [Reduced Result];

你可以很容易地适应这种技术，把它变成一个函数-只要确保对NULL值和整数做适当的错误检查。