两表。
表A
ID,TITLE,CONTENT
1 A组 A,B,C
2 B组 a,b,c
3 C组 1,2,3
4 D组 一,二,三表B
bid , btitle ,bcontent
1 甲 a,1
2 乙 B,c,1,一
3 丙 1,一,A,B,a,b现在要求
如果调出甲的记录则输出
"甲,B组=a,C组=1"如果调出乙的记录则输出
"乙,A组=B,B组=c,C组=1,D组=一"如果调出丙的记录则输出
"丙,A组=AB,B组=ab,C组=1,D组=一"谢谢。
表A
ID,TITLE,CONTENT
1 A组 A,B,C
2 B组 a,b,c
3 C组 1,2,3
4 D组 一,二,三表B
bid , btitle ,bcontent
1 甲 a,1
2 乙 B,c,1,一
3 丙 1,一,A,B,a,b现在要求
如果调出甲的记录则输出
"甲,B组=a,C组=1"如果调出乙的记录则输出
"乙,A组=B,B组=c,C组=1,D组=一"如果调出丙的记录则输出
"丙,A组=AB,B组=ab,C组=1,D组=一"谢谢。
--先创建一个函数/*按照符号分割字符串*/
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
-- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))
end
insert @t(col) values (@c)
return
endgo
declare @表A table (ID int,TITLE varchar(3),CONTENT varchar(10))
insert into @表A
select 1,'A组','A,B,C' union all
select 2,'B组','a,b,c' union all
select 3,'C组','1,2,3' union all
select 4,'D组','一,二,三'declare @表B table(bid int, btitle varchar(4),bcontent varchar(12))
insert into @表B
select 1,'甲','a,1' union all
select 2,'乙','B,c,1,一' union all
select 3,'丙','1,一,A,B,a,b'
declare @tj varchar(20);set @tj='甲'
declare @sql varchar(8000)select @sql=isnull(@sql+',','')+title+'='+col from
@表A a left join
dbo.[m_split]((select bcontent from @表B where btitle=@tj),',')
b on charindex(','+ltrim(ascii(b.col))+',',','+ltrim(ascii(a.CONTENT))+',')>0
where b.col is not null
order by a.titleselect @sql as 结果
/*
B组=a,C组=1
*/提个思路,细节上你可以自己改下。
insert into t1 select 1,'A组','A,B,C'
insert into t1 select 2,'B组','a,b,c'
insert into t1 select 3,'C组','1,2,3'
insert into t1 select 4,'D组','一,二,三'
create table t2(bid int,btitle nvarchar(10),bcontent nvarchar(20))
insert into t2 select 1,'甲','a,1'
insert into t2 select 2,'乙','B,c,1,一'
insert into t2 select 3,'丙','1,一,A,B,a,b'
go
;with c1 as(
select C.*,t1.title from(
select a.bid,a.btitle,b.bcontent from(
select bid,btitle,bcontent = CONVERT(xml,'<root><v>' + REPLACE(bcontent, ',', '</v><v>') + '</v></root>') FROM t2
)A
OUTER APPLY(
SELECT bcontent = N.v.value('.', 'varchar(100)') FROM A.bcontent.nodes('/root/v') N(v)
)B
)C inner join t1 on charindex(c.bcontent,t1.content collate Chinese_PRC_CS_AS)>0
),c2 as(
select bid,btitle,title,stuff((select ' '+bcontent from c1 where bid=a.bid and title=a.title for xml path('')),1,1,'')as t from c1 a group by bid,btitle,title
),c3 as(
select bid,btitle,title+'='+replace(t,' ','') t from c2
)select distinct btitle,stuff((select ','+t from c3 where btitle=a.btitle for xml path('')),1,1,'')as title from c3 a
/*
btitle title
---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
丙 A组=AB,B组=ab,C组=1,D组=一
甲 B组=a,C组=1
乙 A组=B,B组=c,C组=1,D组=一(3 行受影响)
*/go
drop table t1,t2
insert into t1 select 1,'A组','A,B,C'
insert into t1 select 2,'B组','a,b,c'
insert into t1 select 3,'C组','1,2,3'
insert into t1 select 4,'D组','一,二,三'
create table t2(bid int,btitle nvarchar(10),bcontent nvarchar(20))
insert into t2 select 1,'甲','a,1'
insert into t2 select 2,'乙','B,c,1,一'
insert into t2 select 3,'丙','1,一,A,B,a,b'
go
;with c1 as(
select C.*,t1.title from(
select a.bid,a.btitle,b.bcontent from(
select bid,btitle,bcontent = CONVERT(xml,'<root><v>' + REPLACE(bcontent, ',', '</v><v>') + '</v></root>') FROM t2
)A
OUTER APPLY(
SELECT bcontent = N.v.value('.', 'varchar(100)') FROM A.bcontent.nodes('/root/v') N(v)
)B
)C inner join t1 on charindex(c.bcontent,t1.content collate Chinese_PRC_CS_AS)>0
),c2 as(
select bid,btitle,title,stuff((select ' '+bcontent from c1 where bid=a.bid and title=a.title for xml path('')),1,1,'')as t from c1 a group by bid,btitle,title
),c3 as(
select bid,btitle,title+'='+replace(t,' ','') t from c2
)select distinct bid,btitle,stuff((select ','+t from c3 where btitle=a.btitle for xml path('')),1,1,'')as title from c3 a order by bid
/*
bid btitle title
----------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 甲 B组=a,C组=1
2 乙 A组=B,B组=c,C组=1,D组=一
3 丙 A组=AB,B组=ab,C组=1,D组=一(3 行受影响)
*/go
drop table t1,t2
select C.*,t1.title from(
--下面一段,是用来把t2表的btitle拆散
select a.bid,a.btitle,b.bcontent from(
select bid,btitle,bcontent = CONVERT(xml,'<root><v>' + REPLACE(bcontent, ',', '</v><v>') + '</v></root>') FROM t2
)A
OUTER APPLY(
SELECT bcontent = N.v.value('.', 'varchar(100)') FROM A.bcontent.nodes('/root/v') N(v)
)B
--拆散后的结果:
/*
bid btitle bcontent
----------- ---------- ----------------------------------------------------------------------------------------------------
1 甲 a
1 甲 1
2 乙 B
2 乙 c
2 乙 1
2 乙 一
3 丙 1
3 丙 一
3 丙 A
3 丙 B
3 丙 a
3 丙 b(12 行受影响)
*/
--拆散后,将bcontent与t1表连接,获取t1表对应的title(以区分大小写的方式)
)C inner join t1 on charindex(c.bcontent,t1.content collate Chinese_PRC_CS_AS)>0
),c2 as(
--再将相同bid,相同组(A组B组等)的bcontent组合起来
select bid,btitle,title,stuff((select ' '+bcontent from c1 where bid=a.bid and title=a.title for xml path('')),1,1,'')as t from c1 a group by bid,btitle,title
),c3 as(
--两列合并,得类似于:A组=AB 的形式
select bid,btitle,title+'='+replace(t,' ','') t from c2
)
--再进一步拼凑得结果
select distinct bid,btitle,stuff((select ','+t from c3 where btitle=a.btitle for xml path('')),1,1,'')as title from c3 a order by bid
/*
bid btitle title
----------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 甲 B组=a,C组=1
2 乙 A组=B,B组=c,C组=1,D组=一
3 丙 A组=AB,B组=ab,C组=1,D组=一(3 行受影响)
*/
--下面一段,是用来把t2表的bcontent拆散