日期:2014-05-17 浏览次数:20463 次
drop table t
go
create table t(id int, ids varchar(100))
insert into t
select 1 , '3,8,83,92,215,7'
go
declare @a varchar(100) = '8,7'
;with tt
as
(
select id, ids,@a+',' as a,ids+',' as ids_t
from t
where ids like ('%' + REPLACE(@a,',', '%') + '%')
),
ttt
as
(
select id,ids,
cast(a as varchar(max)) as a,
cast(ids_t as varchar(max)) as ids_t ,
1 as level
from tt
union all
select id,ids,
cast(stuff(a,1,charindex(',',a),'') as varchar(max)) ,
cast(replace(ids_t,left(a,charindex(',',a)),'') as varchar(max)),
level + 1
from ttt
where charindex(',',a) > 0
)
select id, ids_t
from
(
select id, ids,left(ids_t,len(ids_t)-1) as ids_t,
ROW_NUMBER() over(partition by id order by level desc) as rownum
from ttt
)a
where rownum = 1
/*
id ids_t
1 3,83,92,215
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2013-11-21 23:19:12
-- Version:
-- Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86)
-- Sep 22 2011 00:28:06
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[ids] varchar(15))
insert [tb]
select 1,'3,8,83,92,215,7'
--------------开始查询--------------------------
Select
a.id,ids=substring(a.ids,b.number,charindex(',',a.ids+',',b.number)-b.number)
into #tb
from
Tb a join master..spt_values b
ON B.type='p'