[MSSQL]最小公约数

[摘要]一个朋友在展BOM的时候有这种需求，两列字段（数值）：A ，B   A=用量，B=底数，组成用量=用量/底数。A/B，若能被整除，显示整除的结果，若不能整除显示分数形式A/B（分数形式要是约分后的格式）3/6=1/3。

我的思路：

1.SQL取余推断能否被整除

2.撰写两个整形数值的最大公约数，然后分子与分母分别除以最大公约数。

当中进行转换化成字符串格式。

一、自己定义最大公约数函数：

Create FUNCTION [dbo].[GetMaxgongyue]

(

	@num1   int ,

	@num2  int

)

RETURNS  int

as

begin

DECLARE @times INT

DECLARE @min INT

DECLARE @result BIGINT

IF( @num1 >= @num2 )

  SET @min=@num2

ELSE

  SET @min=@num1

SET @times=@min

WHILE(@times <= @min )

  BEGIN

      IF( @num1%@times = 0

          AND @num2%@times = 0 )

        BEGIN

            SET @result=@times

            BREAK

        END

      SET @times=@times - 1

  END

return @result

end

二、測试

drop table tt

Create table tt(t1  int,t2  int)

insert into  tt(t1 ,t2 )values(1,3)

insert into  tt(t1 ,t2 )values(2,4)

insert into  tt(t1 ,t2 )values(3,9)

insert into  tt(t1 ,t2 )values(6,3)

insert into  tt(t1 ,t2 )values(4,2)

insert into  tt(t1 ,t2 )values(2,331)

select t1,t2,t1%t2 取余,

case when t1%t2<>0 and dbo.GetMaxgongyue(t1,t2)=1 then CAST(t1 as NCHAR(4))+'/'+ CAST(t2 as NCHAR(4))

     when t1%t2<>0 and dbo.GetMaxgongyue(t1,t2)<>1 then CAST(t1/dbo.GetMaxgongyue(t1,t2) as NCHAR(4))+'/'+ CAST(t2/dbo.GetMaxgongyue(t1,t2) as NCHAR(4))

else cast(cast(t1/t2 as decimal(9,5)) as NCHAR(20)) end result

from tt