34,590
社区成员
发帖
与我相关
我的任务
分享
tba
ID classid name
1 1,2,3 西服
2 2,3 中山装
3 1,3 名裤
tbb
id classname
1 衣服
2 上衣
3 裤子
我得的结果是
id classname name
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
create table tba(ID int,classid varchar(20),name varchar(10))
insert into tba values(1,'1,2,3','西服')
insert into tba values(2,'2,3' ,'中山装')
insert into tba values(3,'1,3' ,'名裤')
create table tbb(ID varchar(10), classname varchar(10))
insert into tbb values('1','衣服')
insert into tbb values('2','上衣')
insert into tbb values('3','裤子')
go
--第1种方法,创建函数来显示
create function f_hb(@id varchar(10))
returns varchar(1000)
as
begin
declare @str varchar(1000)
set @str=''
select @str=@str+','+[classname] from tbb where charindex(','+cast(id as varchar)+',',','+@id+',')>0
return stuff(@str,1,1,'')
end
go
select id,classid=dbo.f_hb(classid),name from tba
drop function f_hb
/*
id classid name
----------- ------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(所影响的行数为 3 行)
*/
--第2种方法.update
while(exists (select * from tba,tbb where charindex(tbb.id,tba.classid) >0))
update tba
set classid= replace(classid,tbb.id,tbb.classname)
from tbb
where charindex(tbb.id,tba.classid)>0
select * from tba
/*
ID classid name
----------- -------------------- ----------
1 衣服,上衣,裤子 西服
2 上衣,裤子 中山装
3 衣服,裤子 名裤
(所影响的行数为 3 行)
*/
drop table tba,tbb
select
t1.ID,t1.userid,t2.UserName
from
b t1
join
a t2 on t1.UserID=t2.UserID
union
select
t1.ID,t1.userid,t2.UserName
from
b t1
join
a t2 on ','+t1.TempUserID+',' like '%,'+t2.UserID+',%'
select b.id , b.userid , a.username from a,b where a.userid = b.userid
union all
select b.id , a.userid , a.username from a,b where charindex(','+a.userid+',',','+b.tempuserid+',') > 0
select b.id , b.userid , username from a,b where a.userid = b.userid
union all
select b.id , a.userid , username from a,b where charindex(','+ltrim(a.userid)+',',','+b.tempuserid+',') > 0
create table A(UserID varchar(10) , UserName varchar(10))
insert into A values('10','张三')
insert into A values('11','李四')
insert into A values('12','王五')
insert into A values('13','杨六')
insert into A values('14','。。。')
create table B(ID varchar(10) , UserID varchar(10) , TempUserID varchar(10))
insert into B values('16', '10', '11,12,13')
go
select b.id , b.userid , a.username from a,b where a.userid = b.userid
union all
select b.id , a.userid , a.username from a,b where charindex(','+a.userid+',',','+b.tempuserid+',') > 0
order by b.id , b.userid
drop table A,B
/*
id userid username
---------- ---------- ----------
16 10 张三
16 11 李四
16 12 王五
16 13 杨六
(所影响的行数为 4 行)
*/
select b.id , b.userid , a.username from a,b where a.userid = b.userid
union all
select b.id , a.userid , a.username from a,b where charindex(','+a.userid+',',','+b.tempuserid+',') > 0