日期:2014-05-17 浏览次数:20785 次
create table #tb(col varchar(100))
insert into #tb
select '03 05 09 15 20 25'
union all select '01 05 13 15 21 25'
union all select '03 05 15 22 24 25'
union all select '05 08 11 13 15 25'
union all select '07 08 15 23 25 32'
union all select '02 13 19 20 26 27'
union all select '04 11 18 19 26 31'
union all select '07 10 17 23 24 32'
go
;with cte as
(
select id=ROW_NUMBER() over(Order by getdate()),col from #tb
),
cte2 as
(
select id,right(ss,1) as No,count(*) as count
from
(select id,substring(col,number,charindex(' ',col+' ',number)-number) as ss
from cte,master..spt_values
where type='P' and number>=1 and number<=len(col) and substring(' '+col,number,1)=' '
)t
group by id,right(ss,1)
having count(*)>=2
union all
select id,a1,count(*)+1 as num
from
(
select id,case when right(ss,1)=left(ss,1) and left(ss,1)<>'0' then left(ss,1) end as a1
from
(
select id,substring(col,number,charindex(' ',col+' ',number)-number) as ss
from cte,master..spt_values
where type='P' and number>=1 and number<=len(col) and substring(' '+col,number,1)=' '
)t
)a
where isnull(a1,'')<>''
group by id,a1
)
select&nbs