两张表联合查询 好久不写 悲剧了解决思路

--> 测试数据：[ta]if object_id('[ta]') is not null drop table [ta]go create table [ta]([id] int,[name] varchar(4))insert [ta]select 1,'张三' union allselect 2,'李四' union allselect 3,'王五' union allselect 4,'赵六'--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([id] int,[name] varchar(5))insert [tb]select 1,'1,2,4' union allselect 2,'2,3'--------------开始查询--------------------------go create function f_hb(@id varchar(10))returns varchar(1000)asbegin  declare @str varchar(1000)  set @str=''  select @[email protected]+','+[name] from [ta] where charindex(','+cast(id as varchar)+',',',[email protected]+',')>0  return stuff(@str,1,1,'')endgo select id,name=dbo.f_hb([name]) from [tb]--drop function f_hb--drop table ta,tb/*
------解决方案--------------------
SQL code
--> 测试数据：[ta]if object_id('[ta]') is not null drop table [ta]go create table [ta]([id] int,[name] varchar(4))insert [ta]select 1,'张三' union allselect 2,'李四' union allselect 3,'王五' union allselect 4,'赵六'--> 测试数据：[tb]if object_id('[tb]') is not null drop table [tb]go create table [tb]([id] int,[name] nvarchar(5),tab1 varchar(20))insert [tb]select 1,N'作者','1,2,4' union allselect 2,N'文章','2,3'select b.ID,       b.name,       stuff((select ','+[name]        from ta a        where CHARINDEX(','+ltrim(a.id)+',',','+b.tab1+',')>0       for XML path('')),1,1,'')from tb b
------解决方案--------------------
探讨


你写的很神奇。
我都没这么写过。
------------------------------
前提是tab1的数据有可能增长，tab2的数据也可能添加新的 东西。

------解决方案--------------------
create table t1
(
	id int,
	name varchar(10)
)
insert into t1
select 1, '张三' union all
select 2, '李四' union all
select 3, '王五' union all
select 4, '赵六'
create table t2
(
	id int,
	name varchar(10),
	tab1 varchar(10)
)
insert into t2
select 1, '作者', '1,2,4' union all
select 2, '文章', '2,3'
select * from t1
select * from t2

select *,STUFF((select ','+name from t1 where CHARINDEX(ltrim(t1.id),ltrim(t2.tab1))>0 for xml path('')),1,1,'') as name  
from t2


----------------------------
id	name	tab1	name
1	作者	1,2,4	张三,李四,赵六
2	文章	2,3	李四,王五
------解决方案--------------------
我想了个简单解法：
select a.*,(
	select name+','  
	from tab1  
	where charindex( cast(id as varchar),a.tab1)>0
   for xml path('')
) c  
from tab2 a
------解决方案--------------------
是个美女啊！

一般会了这个就牛b了
我也在学习中，多多交流！