简单的话创建一个view,把table1的B和table2的B关联起来,table1的C和table3的C关联起来
select case when b is not null and c is not null then 'B'+ B+ '('+BN+')'+'C'+ C+ '('+CN+')' when b is not null and c is null then 'B'+ B+ '('+BN+')'
when b is null and c is not null then 'C'+ C+ '('+CN+')' end from view1 where ...
select case when b is not null and c is not null then 'B'+ B+ '('+BN+')'+'C'+ C+ '('+CN+')' when b is not null and c is null then 'B'+ B+ '('+BN+')'
when b is null and c is not null then 'C'+ C+ '('+CN+')' end from view1 where ...
union
select 'C'+t2.c+'('+t2.cn+')' from test2,t2 where t2.c=test2.c and test2.a=2 and test2.b is null and test2.c is not null
union
select 'B'+t1.b+'('+t1.bn+')'+'C'+t2.c+'('+t2.cn+')' from test2,t1,t2 where t1.b=test2.b and t2.c=test2.c and test2.a=1 and test2.b is not null and test2.c is not nullt1表是你的表2
t2表是你的表3
test2是你的表1
as
begin
set nocount on
select 'B'+t1.b+'('+t1.bn+')' from test2,t1 where t1.b=test2.b and test2.a=@a and test2.b is not null and test2.c is null
union
select 'C'+t2.c+'('+t2.cn+')' from test2,t2 where t2.c=test2.c and test2.a=@a and test2.b is null and test2.c is not null
union
select 'B'+t1.b+'('+t1.bn+')'+'C'+t2.c+'('+t2.cn+')' from test2,t1,t2 where t1.b=test2.b and t2.c=test2.c and test2.a=@a and test2.b is not null and test2.c is not null
set nocount off
end