create table #tab1 (aID INT,b VARCHAR(10))
INSERT INTO #tab1 values(1,'1,2')
insert into #tab1 values(2,'2,3,5')
insert into #tab1 values(3,'6')
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
select * from #
/*
先看substring的条件当aID为1的时候那么b就为1,2
此时在看substring(',1,2',1,1)=','条件成立,那么此时b.id=1
在看CHARINDEX(',','1,2,',1)结果为2减去b.id的1,在看substring(1,'1,2',1)那就是1
单d.id为2的时候看substring(',1,2',2,1)=1而不是','所以不满足,在看3满足条件
*/
SELECT A.aID, SUBSTRING(A.b, B.id, CHARINDEX(',', A.b + ',', B.id) - B.id)
FROM tab1 A, # B
WHERE SUBSTRING(',' + A.b, B.id, 1) = ','
--小梁的
if object_id('tb') is not null
drop table tb
go
create table tb(id int,pid varchar(50))
go
insert into tb select 1,'3,4,5,6,7,' union all
select 2,'4,5,6,7,' union all
select 3,'1,3,5,7,'
go
;with liang as
(
select distinct pid from
(
select id,
pid= substring(pid,number,charindex(',',pid,number)-number)
from tb,master..spt_values a
where a.type = 'P'
and substring(','+pid,number,1)=','
and number <len(pid)
)T
)
select id =(row_number() over(order by pid)),pid from liang
drop table tb