sql 表内字段拆分查询

查询效果：

 

 

 

第一种方式，构造临时表，一行一行读取

 

select * into #t from DepartureUsers
create table #t1(
id varchar(10),
userId varchar(10)
)
while exists (select * from #t)
begin
insert #t1 select id,case when charindex(',',UserIdStr)>0 then left(UserIdStr,charindex(',',UserIdStr)-1) else UserIdStr end as userId from #t
update #t set UserIdStr=case when charindex(',',UserIdStr)>0 then substring(UserIdStr,charindex(',',UserIdStr)+1,8000) else '' end
delete #t where UserIdStr=''
end
drop table #t

select a.id,A.UserIdStr ,B.userId
from DepartureUsers a,#t1 b
where a.id=b.id

drop table #t1

 

 

 

第二种方式：

先创建split方法

Create function [dbo].[f_split](@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date :2020-5-23
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
GO

再进行查询

 

declare m_cursor cursor scroll for
select id,UserIdStr from DepartureUsers where UserIdStr<>'' and IsDeleted=0
open m_cursor
declare @id varchar(10),@UserIdStr varchar(500)

fetch next from m_cursor into @id,@UserIdStr
while @@FETCH_STATUS=0
begin
INSERT INTO #TEMPl
select @id,a,@UserIdStr
from dbo.f_split(@UserIdStr,',')
fetch next from m_cursor into @id,@UserIdStr
end

close m_cursor
deallocate m_cursor
select * from #TEMPl

end
drop table #TEMPl