create table taba (a_1 int, a_2 int)insert into taba select 3, 2 union all select 4, 6 union all select 7, 8create function fnyf (@a1 int,@a2 int) returns varchar(15) as begin declare @m int,@i int,@b1 int,@b2 int select @m=case when @a1>=@a2 then @a2 else @a1 end,@i=2,@b1=@a1,@b2=@a2 while(@i<=@m) begin if @b1%@i=0 and @b2%@i=0 select @b1=@b1/@i,@b2=@b2/@i select @i=@i+1 end return cast(@b1 as varchar)+':'+cast(@b2 as varchar) end select dbo.fnyf(a_1,a_2) 'a_1:a_2' from tabaa_1:a_2 --------------- 3:2 2:3 7:8(3 row(s) affected)
--> 测试数据: @表A declare @表A table (a_1 int,a_2 int) insert into @表A select 3,2 union all select 12,18 union all --改成12和18 测试约分 select 7,8declare @i int,@j int select @i=max(a_1),@j=max(a_2) from @表A;with maco as ( select number from master..spt_values where type='p' and (number between 2 and @i or number between 2 and @j)) select isnull(b.c1,a.a_1) as a_1,isnull(b.c2,a.a_2) as a_2 from @表A a left join ( select min(a_1/number) as c1,min(a_2/number) as c2,a_1,a_2 from maco a,@表A b where a_1%number=0 and a_2%number=0 group by a_1,a_2)b on a.a_1=b.a_1 and a.a_2=b.a_2/* a_1 a_2 ----------- ----------- 3 2 2 3 7 8 */
create table taba
(a_1 int, a_2 int)insert into taba
select 3, 2 union all
select 4, 6 union all
select 7, 8create function fnyf
(@a1 int,@a2 int)
returns varchar(15)
as
begin
declare @m int,@i int,@b1 int,@b2 int
select @m=case when @a1>=@a2 then @a2 else @a1 end,@i=2,@b1=@a1,@b2=@a2
while(@i<=@m)
begin
if @b1%@i=0 and @b2%@i=0
select @b1=@b1/@i,@b2=@b2/@i
select @i=@i+1
end
return cast(@b1 as varchar)+':'+cast(@b2 as varchar)
end
select dbo.fnyf(a_1,a_2) 'a_1:a_2' from tabaa_1:a_2
---------------
3:2
2:3
7:8(3 row(s) affected)
--> 测试数据: @表A
declare @表A table (a_1 int,a_2 int)
insert into @表A
select 3,2 union all
select 12,18 union all --改成12和18 测试约分
select 7,8declare @i int,@j int
select @i=max(a_1),@j=max(a_2) from @表A;with maco as (
select number from master..spt_values where type='p'
and (number between 2 and @i or number between 2 and @j))
select isnull(b.c1,a.a_1) as a_1,isnull(b.c2,a.a_2) as a_2 from @表A a
left join (
select min(a_1/number) as c1,min(a_2/number) as c2,a_1,a_2
from maco a,@表A b where a_1%number=0 and a_2%number=0 group by a_1,a_2)b
on a.a_1=b.a_1 and a.a_2=b.a_2/*
a_1 a_2
----------- -----------
3 2
2 3
7 8
*/