? ? ? ?[摘要]一個(gè)朋友在展BOM的時(shí)候有這種需求，兩列字段（數(shù)值）：A ，B ? A=用量，B=底數(shù)，組成用量=用量/底數(shù)。A/B，若能被整除，顯示整除的結(jié)果，若不能整除顯示分?jǐn)?shù)形式A/B（分?jǐn)?shù)形式要是約分后的格式）3/6=1/3。

? ? ? 我的思路：

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

2.撰寫兩個(gè)整形數(shù)值的最大公約數(shù)，然后分子與分母分別除以最大公約數(shù)。

當(dāng)中進(jìn)行轉(zhuǎn)換化成字符串格式。

一、自己定義最大公約數(shù)函數(shù)：

      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