新建函数 CREATE FUNCTION dbo.F_Split ( @SplitString nvarchar(max), --源字符串 @Separator nvarchar(10)=' ' --分隔符号,默认为空格 ) RETURNS @SplitStringsTable TABLE --输出的数据表 ( [id] int identity(1,1), [value] nvarchar(max) ) AS BEGIN DECLARE @CurrentIndex int; DECLARE @NextIndex int; DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1; WHILE(@CurrentIndex<=len(@SplitString)) BEGIN SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex); IF(@NextIndex=0 OR @NextIndex IS NULL) SELECT @NextIndex=len(@SplitString)+1; SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex); INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText); SELECT @CurrentIndex=@NextIndex+1; END RETURN; END GO --测试数据 if not object_id(N'Tempdb..#A') is null drop table #A Go Create table #A([id] int,[name] nvarchar(21)) Insert #A select 1,N'a' union all select 2,N'b' union all select 3,N'c' union all select 4,N'd' GO if not object_id(N'Tempdb..#B') is null drop table #B Go Create table #B([id] int,[expand] nvarchar(25)) Insert #B select 1,N'1,2' union all select 2,N'2,3,4' union all select 3,N'1,3,4' Go --测试数据结束 SELECT id , STUFF(( SELECT ',' + name FROM #A JOIN dbo.F_Split(expand, ',') t ON t.value = #A.id FOR XML PATH('') ), 1, 1, '') AS expand FROM #B
USE tempdb GO IF OBJECT_ID('A') IS NOT NULL DROP TABLE A IF OBJECT_ID('B') IS NOT NULL DROP TABLE B CREATE TABLE A(id INT,[name] VARCHAR(100)) CREATE TABLE B(id INT,[expand] VARCHAR(100)) SET NOCOUNT ON; INSERT INTO A VALUES (1,'a') INSERT INTO A VALUES (2,'b') INSERT INTO A VALUES (3,'c') INSERT INTO A VALUES (4,'d') INSERT INTO B VALUES (1,'1,2') INSERT INTO B VALUES (2,'2,3,4') INSERT INTO B VALUES (3,'1,3,4')SELECT b.id, STUFF( ( SELECT ','+a.[name] FROM ( SELECT [value] = CONVERT(XML, '<v>' + REPLACE(B.[expand], ',', '</v><v>') + '</v>') ) aa OUTER APPLY( SELECT id = N.v.value('.', 'nvarchar(max)') FROM aa.[value].nodes('/v') N(v) ) bb INNER JOIN A ON bb.id=a.id WHERE ISNULL(B.id,'')!='' FOR XML PATH('') ),1,1,'') AS expandName FROM B /* id expandName 1 a,b 2 b,c,d 3 a,c,d */
with a as ( select 1 as id,'a' as name union all select 2,'b' union all select 3,'c' union all select 4,'d' ),b as ( select 1 as id,'1,2' as expand union all select 2,'2,3,4' union all select 3,'1,3,4' ) select * from b cross apply( select stuff((select ','+name from a where charindex(','+convert(varchar(max),id)+',',','+b.expand+',')>0 for xml path('')),1,1,'') as ex ) c
CREATE FUNCTION dbo.F_Split
(
@SplitString nvarchar(max), --源字符串
@Separator nvarchar(10)=' ' --分隔符号,默认为空格
)
RETURNS @SplitStringsTable TABLE --输出的数据表
(
[id] int identity(1,1),
[value] nvarchar(max)
)
AS
BEGIN
DECLARE @CurrentIndex int;
DECLARE @NextIndex int;
DECLARE @ReturnText nvarchar(max); SELECT @CurrentIndex=1;
WHILE(@CurrentIndex<=len(@SplitString))
BEGIN
SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
IF(@NextIndex=0 OR @NextIndex IS NULL)
SELECT @NextIndex=len(@SplitString)+1;
SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);
SELECT @CurrentIndex=@NextIndex+1;
END
RETURN;
END
GO
--测试数据
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[name] nvarchar(21))
Insert #A
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c' union all
select 4,N'd'
GO
if not object_id(N'Tempdb..#B') is null
drop table #B
Go
Create table #B([id] int,[expand] nvarchar(25))
Insert #B
select 1,N'1,2' union all
select 2,N'2,3,4' union all
select 3,N'1,3,4'
Go
--测试数据结束
SELECT id ,
STUFF(( SELECT ',' + name
FROM #A
JOIN dbo.F_Split(expand, ',') t ON t.value = #A.id
FOR
XML PATH('')
), 1, 1, '') AS expand
FROM #B
GO
IF OBJECT_ID('A') IS NOT NULL DROP TABLE A
IF OBJECT_ID('B') IS NOT NULL DROP TABLE B
CREATE TABLE A(id INT,[name] VARCHAR(100))
CREATE TABLE B(id INT,[expand] VARCHAR(100))
SET NOCOUNT ON;
INSERT INTO A VALUES (1,'a')
INSERT INTO A VALUES (2,'b')
INSERT INTO A VALUES (3,'c')
INSERT INTO A VALUES (4,'d')
INSERT INTO B VALUES (1,'1,2')
INSERT INTO B VALUES (2,'2,3,4')
INSERT INTO B VALUES (3,'1,3,4')SELECT b.id,
STUFF(
(
SELECT ','+a.[name]
FROM (
SELECT [value] = CONVERT(XML, '<v>' + REPLACE(B.[expand], ',', '</v><v>') + '</v>')
) aa
OUTER APPLY(
SELECT id = N.v.value('.', 'nvarchar(max)')
FROM aa.[value].nodes('/v') N(v)
) bb
INNER JOIN A ON bb.id=a.id
WHERE ISNULL(B.id,'')!=''
FOR XML PATH('')
),1,1,'') AS expandName
FROM B
/*
id expandName
1 a,b
2 b,c,d
3 a,c,d
*/
select 1 as id,'a' as name
union all
select 2,'b'
union all
select 3,'c'
union all
select 4,'d'
),b as (
select 1 as id,'1,2' as expand
union all
select 2,'2,3,4'
union all
select 3,'1,3,4'
)
select * from b
cross apply(
select stuff((select ','+name from a where charindex(','+convert(varchar(max),id)+',',','+b.expand+',')>0 for xml path('')),1,1,'') as ex
) c