/*
tabTemp表
id
1
2
3,4
5,6,7
在查询中显示
1
2
3
4
5
6
7
ids表(临时表,存储tabTemp中id不是一个的数据)
*/
alter proc proc_splitstr
@SourceSql varchar(50),
@StrSeprate varchar(5)
as
begin
declare @i int
declare @temp varchar(10)
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
set @temp = left(@SourceSql,@i-1)
insert ids values (@temp)
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'' insert ids values(@SourceSql)
end
-------------------------------------------------
create proc usp_getIds
as
declare @id varchar(50)
DECLARE Temp_Cursor CURSOR FOR
SELECT id
FROM tabTemp
where charindex(',',id) > 0;
OPEN Temp_Cursor;
FETCH NEXT FROM Temp_Cursor into @id;
WHILE @@FETCH_STATUS = 0
BEGIN
exec proc_splitstr @id,','
FETCH NEXT FROM Temp_Cursor into @id
END;
CLOSE Temp_Cursor;
DEALLOCATE Temp_Cursor;
-------------------------------------------------------
/*直接调用usp_getIds然后使用下面的语句查询*/
select id from tabTemp where charindex(',',id) = 0
union
select id from ids