27,579
社区成员
发帖
与我相关
我的任务
分享
--在.net版有人问了这个问题,我用C#写了(写法见博客),但感觉sql中也可以,于是试了试,结果可以,原理跟那个一样!
--请各位给点意见!
---SQL实现完全排列组合
create function F_strSpit(@s varchar(200)) returns table
as
return(
select value=substring(@s,i,num)+substring(@s,num-1+j,1)
from (select num=number from spt_values where type='p' and number<len(@s) and number>0)TA,
(select i=number+1 from spt_values where type='p' and number<len(@s)-1)TB,
(select j=i+1 from (select i=number+1 from spt_values where type='p' and number<len(@s)-1)g)TC
where num-1+j<=len(@s) and j>i )
declare @s varchar(200)
set @s='ABCE'
select * from dbo.F_strSpit(@s) order by len(value),value
/*
value
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AB
AC
AE
BC
BE
CE
ABC
ABE
BCE
ABCE
(所影响的行数为 10 行)
*/
drop function F_strSpit
drop function F_strSpit12
go
create function F_strSpit12(@s varchar(200))
returns @t table(col varchar(5))
as
begin
insert into @t select substring(@s,number,1)
from v_getnumber where number <=len(@s)
while not exists(select 1 from @t where col = @s)
insert @t
-- output inserted.*--inserted.col, inserted.col
-- into @tt
SELECT T3.COL + T2.COL FROM @t AS T2,@t AS T3 WHERE len(t3.col) = 1 and charindex(T3.COL,T2.COL) =0 and T2.COL > T3.COL
return
end
go
declare @s varchar(200)
set @s='ABCD'
select distinct col,len(col)
from dbo.F_strSpit12(@s)
where len(col) > 1
order by len(col),col
/*
col
----- -----------
AB 2
AC 2
AD 2
BC 2
BD 2
CD 2
ABC 3
ABD 3
ACD 3
BCD 3
ABCD 4
(11 行受影响)
*/