我有两个表：
A表有两个字段 ano, aname
P01 上学
P02 下课
P03 上课
P04 放学
B表有5个字段：bid, b1, b2, b3, b4
1 P01 P04 P02 P03
2 P02 P03 P04 P01
显示B表的时候需要将里面的编号(P01 ano)全部换成A表中的aname
这语句要怎么写？
------解决方案--------------------
WITH A (ano,aname) AS
(
SELECT 'P01','上学' UNION ALL
SELECT 'P02','下课' UNION ALL
SELECT 'P03','上课' UNION ALL
SELECT 'P04','放学'
)
,B (bid, b1, b2, b3, b4) AS
(
SELECT 1,'P01','P04','P02','P03' UNION ALL
SELECT 2,'P02','P03','P04','P01'
)
SELECT bid
,(SELECT aname FROM A WHERE ano=B.b1) b1
,(SELECT aname FROM A WHERE ano=B.b2) b2
,(SELECT aname FROM A WHERE ano=B.b3) b3
,(SELECT aname FROM A WHERE ano=B.b4) b4
FROM B
------解决方案--------------------
下面，分析一下关联表，和子查询，这2种方式的效率：
--drop table A
--drop table B
create table A(ano varchar(20), aname varchar(20))
insert into A
select 'P01', '上学' union all
select 'P02', '下课' union all
select 'P03', '上课' union all
select 'P04', '放学'
create table B(
bid int,
b1 varchar(20),
b2 varchar(20),
b3 varchar(20),
b4 varchar(20)
)
insert into B
select 1 , 'P01', 'P04', 'P02', 'P03' union all
select 2 , 'P02', 'P03', 'P04', 'P01'
set statistics io on
--方法1,关联表
select b.bid,
a1.aname as b1,
a2.aname as b2,
a3.aname as b3,
a4.aname as b4
from B
left join A a1
on b.b1 = a1.ano
left join A a2
on b.b2 = a2.ano
left join A a3
on b.b3 = a3.ano
left join A a4
on b.b4 = a4.ano
/*
bid aname aname aname aname
1 上学 放学 下课 上课
2 下课 上课 放学 上学
*/
--方法2,引用了上面的代码,子查询
SELECT bid
,(SELECT aname FROM A WHERE ano=B.b1) b1
,(SELECT aname FROM A WHERE ano=B.b2) b2
,(SELECT aname FROM A WHERE ano=B.b3) b3
,(SELECT aname FROM A WHERE ano=B.b4) b4
FROM B
下面是2个方法的执行计划，从执行计划来看，每个执行计划的开销，都占了50%，基本上来说两者的效率，差不多。
1.关联表：
2.子查询
