22,207
社区成员
发帖
与我相关
我的任务
分享
with tb1(id,name)as(
select 1,'张三1' union all
select 2,'张三2' union all
select 3,'张三3' union all
select 4,'张三4' union all
select 5,'张三5' union all
select 6,'张三6' ),
cte(id,code) as(
select '1,2,3','aa,cc,dd' union all
select '4,5,6','ee,hh,ss'
)
create function getIdByCode(@ids varchar(200),@codes varchar(200),@code varchar(200)) returns varchar(10)
as
begin
select @ids=','+@ids+',',@codes=','+@codes+',';
declare @id varchar(10)='';
select @id=t1.vl from (select substring(@ids,number+1,charindex(',',@ids,number+1)-number-1) as vl,ROW_NUMBER()over(order by number)as rownum from master..spt_values where type='p' and number between 1 and len(@ids)-1 and substring(@ids,number,1)=',')t1
join(select substring(@codes,number+1,charindex(',',@codes,number+1)-number-1) as vl,ROW_NUMBER()over(order by number)as rownum from master..spt_values where type='p' and number between 1 and len(@codes)-1 and substring(@codes,number,1)=',')t2 on t1.rownum=t2.rownum
where t2.vl=@code
return @id;
end
ALTER FUNCTION dbo.f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
--测试数据
if not object_id(N'Tempdb..#tb1') is null
drop table #tb1
Go
Create table #tb1([id] INT,name NVARCHAR(100))
Insert #tb1
select 1,'张三1' union all
select 2,'张三2' union all
select 3,'张三3' union all
select 4,'张三4' union all
select 5,'张三5' union all
select 6,'张三6'
GO
if not object_id(N'Tempdb..#cte') is null
drop table #cte
Go
Create table #cte([id] NVARCHAR(100),[code] nvarchar(28))
Insert #cte
select '1,2,3',N'aa,cc,dd' union all
select '4,5,6',N'ee,hh,ss'
Go
--测试数据结束
DECLARE @str NVARCHAR(100)='hh'
SELECT #tb1.name
FROM ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY id ) AS num
FROM #cte a
CROSS APPLY ( SELECT col
FROM dbo.f_splitSTR(a.id, ',')
) t1
) tt1
JOIN ( SELECT * ,
ROW_NUMBER() OVER ( ORDER BY id ) AS num
FROM #cte a
CROSS APPLY ( SELECT col
FROM dbo.f_splitSTR(a.code, ',')
) t2
) tt2 ON tt2.num = tt1.num
JOIN #tb1 ON #tb1.id = tt1.col
WHERE tt2.col = @str