求一简单查询语句，谢谢
表a1,

code name sort_code
1 张三 123
2 李四 12
3 王胖 1234


表a2
a2_code a2_pic
1 ★
2 ▲
3
4 *


请问查询张三的记录时能否得到类似于下面的这张表


code name sort_code a2_pic
1 张三 123 ★▲


谢谢，分不多，可再加



[解决办法]
alter table a1 add picture varchar(200)
update a1 set picture=picture+a2.a2_pic from a1,a2 where charindex(a2.a2_code,a1.sort_code) > 0
select * from a1
[解决办法]


---创建环境：

create table a1(code int,name varchar(20),sort_code varchar(20))
insert into a1(code,name,sort_code)
select 1, '张三 ', '123 '
union all
select 2, '李四 ', '12 '
union all
select 3, '王胖 ', '1234 '

select * from a1

/*
codenamesort_code
-----------------------------------
1张三123
2李四12
3王胖1234

*/


create table a2(a2_code int,a2_pic varchar(20))
insert into a2(a2_code, a2_pic)
select 1, '★ '
union all
select 2, '▲ '
union all
select 3, ' '
union all
select 4, '* '

select * from a2
/*

a2_code a2_pic
-------------------------------------
1★
2▲
3
4*
*/

--创建一个函数：f_str_rep

alter function f_str_rep(@str varchar(20))
returns varchar(20)
as
begin
declare @exec varchar(200)
set @exec=@str
set @exec=replace(@exec, '1 ', '★ ')
set @exec=replace(@exec, '2 ', '▲ ')
set @exec=replace(@exec, '3 ', ' ')
set @exec=replace(@exec, '4 ', '1 ')
return (@exec)
end


select *,dbo.f_str_rep(sort_code ) as a2_pic from a1

/*
code name sort_code a2_pic
----------------------------
1张三123★▲
2李四12★▲
3王胖1234★▲1
*/

drop table a1
drop table a2
[解决办法]
drop table a1

建表：

create table a1(code int,name varchar(20),sort_code varchar(20))
insert into a1(code,name,sort_code)
select 1, '张三 ', '123 '
union all
select 2, '李四 ', '12 '
union all
select 3, '王胖 ', '1234 '

select * from a1

/*
codenamesort_code
-----------------------------------
1张三123
2李四12
3王胖1234

*/


create table a2(a2_code int,a2_pic varchar(20))
insert into a2(a2_code, a2_pic)
select 1, '★ '
union all
select 2, '▲ '
union all
select 3, ' '
union all
select 4, '* '

select * from a2
/*

a2_code a2_pic
-------------------------------------
1★
2▲
3
4*
*/

--创建一个函数：f_str_rep

alter function f_str_rep(@str varchar(20))
returns varchar(20)
as
begin
declare @exec varchar(200)
set @exec=@str
set @exec=replace(@exec, '1 ', '★ ')
set @exec=replace(@exec, '2 ', '▲ ')
set @exec=replace(@exec, '3 ', ' ')
set @exec=replace(@exec, '4 ', '* ')
return (@exec)
end


select *,dbo.f_str_rep(sort_code ) as a2_pic from a1

/*
code name sort_code a2_pic
----------------------------
1张三123★▲
2李四12★▲
3王胖1234★▲*
*/

drop table a1
drop table a2
[解决办法]

--根据数据库的变化动态的变换图像

if(object_id( 'a1 ')is not null )
drop table a1
if(object_id( 'a2 ')is not null )
drop table a2

---创建环境：

create table a1(code int,name varchar(20),sort_code varchar(20))
insert into a1(code,name,sort_code)
select 1, '张三 ', '123 '
union all
select 2, '李四 ', '12 '
union all
select 3, '王胖 ', '1234 '

select * from a1

create table a2(a2_code int,a2_pic varchar(20))
insert into a2(a2_code, a2_pic)
select 1, '★ '
union all
select 2, '▲ '
union all
select 3, ' '
union all
select 4, '* '

select * from a2


--创建一个函数：f_str_rep
go
Create function f_str_rep(@str varchar(200))
returns varchar(200)
as
begin
declare @n int
declare @temp nvarchar(20)
declare @returnString nvarchar(200)
set @returnString= ' '
set @n=len(@str)
while (@n > 0)
begin
set @temp=left(@str,1)
select @temp=a2_pic from a2 where a2_code=@temp

set @returnString=@returnString+@temp
--set @str=right(@str,@n-1)--
set @str=ltrim(STUFF(@str,1,1, ' '))
set @n=@n-1
end

return @returnString
end

go
select *,dbo.f_str_rep(sort_code ) as a2_pic from a1

drop table a1
drop table a2