[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

转载于:https://www.cnblogs.com/zfyouxi/p/4309788.html