--查询语句
select a.id,a.Tname
,Color=b.Color+case when c.Color is null then '' else '\'+c.Color end
from 表二 a
left join 表一 b on b.dm=case charindex('/',a.Color) when 0 then a.Color else left(a.Color,charindex('/',a.Color)-1) end
left join 表一 c on c.dm=case charindex('/',a.Color) when 0 then '' else substring(a.Color,charindex('/',a.Color)+1,8000) end
create table 表一(dm varchar(1),color varchar(4))
insert 表一 select 'R','红色'
union all select 'G','绿色'
union all select 'B','黑色'
union all select 'P','紫色'
union all select 'k','银色'create table 表二(id int,Tname varchar(10),color varchar(10))
insert 表二 select 1,'XXX' ,'R/G'
union all select 2,'DsaA','K/B'
union all select 3,'dfs' ,'P/R'
union all select 4,'sks' ,'K'
go--查询语句
select a.id,a.Tname
,Color=b.Color+case when c.Color is null then '' else '\'+c.Color end
from 表二 a
left join 表一 b on b.dm=case charindex('/',a.Color) when 0 then a.Color else left(a.Color,charindex('/',a.Color)-1) end
left join 表一 c on c.dm=case charindex('/',a.Color) when 0 then '' else substring(a.Color,charindex('/',a.Color)+1,8000) end
go--删除测试
drop table 表一,表二/*--测试结果
id Tname Color
----------- ---------- ---------
1 XXX 红色\绿色
2 DsaA 银色\黑色
3 dfs 紫色\红色
4 sks 银色(所影响的行数为 4 行)--*/
,Color=b.Color+case when c.Color is null then '' else '/'+c.Color end
from 表二 a
left join 表一 b on a.Color+'/' like b.dm+'/%'
left join 表一 c on a.Color like '%/'+c.dm
returns varchar(10)
as
begin
declare @a varchar(10)
select @a=replace(@color,code,color)
from dm
return(@a)
end
godeclare @a varchar(10)
set @a='K/B'
select @a=replace(@a,code,color)
from dm
select @a--返回:
銀色/黑色
,isnull((select color
from 表一
where charindex(表一.dm,表二.color)> 0
and charindex(表一.dm,表二.color) < charindex('/',表二.color)),'')
+ case when charindex('/',表二.color) > 0 then '/' else '' end +
isnull((select color
from 表一
where charindex(表一.dm,表二.color)>charindex('/',表二.color)),'')
from 表二