22,207
社区成员
发帖
与我相关
我的任务
分享
create table tb(ic1 int,ic2 int,ic3 int,ic4 int,ic5 int,ic6 int)
insert into tb
select 1,2,3,4,5,6 union all
select 2,12,12,13,24,26 union all
select 1,11,111,23,24,25
go
create function dbo.fn_test
(
@RSCP1 nvarchar(10),
@RSCP2 nvarchar(10),
@RSCP3 nvarchar(10),
@RSCP4 nvarchar(10),
@RSCP5 nvarchar(10),
@RSCP6 nvarchar(10)
)
returns nvarchar(100)
as
begin
declare @tb table(num int)
declare @te table(id int identity(1,1),num int)
declare @strtemp int
insert into @tb
select @RSCP1 union all select @RSCP2 union all
select @RSCP3 union all select @RSCP4 union all
select @RSCP5 union all select @RSCP6
insert into @te
select num from @tb order by num desc
select @strtemp = (select num from @te where id = 1) - (select num from @te where id = 3)
return @strtemp
end
go
select *
from tb
where dbo.fn_test(ic1,ic2,ic3,ic4,ic5,ic6) >= 5
drop function fn_test
drop table tb
/*
ic1 ic2 ic3 ic4 ic5 ic6
----------- ----------- ----------- ----------- ----------- -----------
2 12 12 13 24 26
1 11 111 23 24 25
(2 行受影响)
create function dbo.fn_test
(
@RSCP1 nvarchar(10),
@RSCP2 nvarchar(10),
@RSCP3 nvarchar(10),
@RSCP4 nvarchar(10),
@RSCP5 nvarchar(10),
@RSCP6 nvarchar(10)
)
returns nvarchar(100)
as
begin
declare @tb table(num int)
declare @strtemp varchar(100)
insert into @tb
select @RSCP1 union all select @RSCP2 union all
select @RSCP3 union all select @RSCP4 union all
select @RSCP5 union all select @RSCP6
select @strtemp = ltrim((select top 1 num from @tb order by num desc) - (select top 1 num from (select top 3 num from @tb order by num desc)t order by num))
return @strtemp
end
go
select dbo.fn_test(1,2,35,6,7,8)
drop function fn_test
/*
----------------------------------------------------------------------------------------------------
28
create function dbo.fn_test
(
@RSCP1 nvarchar(10),
@RSCP2 nvarchar(10),
@RSCP3 nvarchar(10),
@RSCP4 nvarchar(10),
@RSCP5 nvarchar(10),
@RSCP6 nvarchar(10)
)
returns nvarchar(100)
as
begin
declare @tb table(num int)
declare @strtemp varchar(100)
insert into @tb
select @RSCP1 union all select @RSCP2 union all
select @RSCP3 union all select @RSCP4 union all
select @RSCP5 union all select @RSCP6
select @strtemp=isnull(@strtemp+',','')+rtrim(num) from @tb order by num
return @strtemp
end
go
select dbo.fn_test(1,2,35,6,7,8)
/*
------------
1,2,6,7,8,35