这种情况如何查询好些?

[dtmyou]

table1
id column11 column12
1 4 2
2 5 3
table2
id column21 column22
1 1 2
2 2 3
3 3 5
4 4 7
5 5 8
table3
id column31 column32
1 1 4
2 2 9
3 3 0
其中table2中的column21唯一,且对应于table1中的column11或table3中的column31
要求得到结果:
id table2id column21 column22 TmpColumn
1 1 1 2 4
2 2 2 3 9
3 3 3 5 0
4 4 4 7 2
5 5 5 8 3
其中id为从1开始的自增字段

PS:我目前解决方法是在table2中增加一个字段btable用来标识该记录在table3或是table1中,1表示在table3中,0表示在table1中
现table2情况如下
id column21 column22 btable
1 1 2 1
2 2 3 1
3 3 5 1
4 4 7 0
5 5 8 0

select table2.id as table2id,column21,column22,
tmpColumn = case when btable = 1 then (select column32 from table3 where column31 = table2.column1) else (select column12 from table1 where column11 = table2.column1) end from table2
(id还没想到怎么去解决)

有没有好些的办法...

多谢,不胜感激!!

[txlicenhe]

select a.*,isnull(b.column12,c.column32)
from table2 a
left join table1 b on a.column21=b.column11
left join table3 c on a.column21=c.column31


or:

Select identity(int,1,1) as id,a.*,isnull(b.column12,c.column32)
into #tmp
from table2 a
left join table1 b on a.column21=b.column11
left join table3 c on a.column21=c.column31

Select * from #tmp

drop table #tmp

[nboys]

select indentity(int,1,1) as id,b.id as table2id,column12,column22,column32 as TmpColumn
into #temp
from
table2 a left join table3 b on a.column21=b.column31
left join table1 c on a.column21=c.column11


select * from #temp

drop table #temp

[nboys]

or

select identity(int,1,1) as id,a.id as table2id,column12,column22,isnull(b.column12,c.column32) as column32
into #temp
from
table2 a full join table1 b on a.column21=b.column11
full join table3 c on a.column21=c.column31
select * from #temp

drop table #temp

[pengdali]

select (select sum(1) from table2 where id<=a.id) [id],a.*,b.column11 from table2 a left join (select column11,column12 from table1 union all select column31,column32 from table3) b on a.column21=b.column11

[zjcxc]

select id,table2id,column21,column22,TmpColumn=isnull(column12,column32)
from(
select id=(select count(*) from @table2 where id<=a.id),
table2id=a.id,a.column21,a.column22,b.column12,c.column32
from @table2 a
left join @table1 b on a.column21=b.column11
left join @table3 c on a.column21=c.column31
) a order by table2id

[zjcxc]

select id,table2id,column21,column22,TmpColumn=isnull(column12,column32)
from(
select id=(select count(*) from table2 where id<=a.id),
table2id=a.id,a.column21,a.column22,b.column12,c.column32
from table2 a
left join table1 b on a.column21=b.column11
left join table3 c on a.column21=c.column31
) a order by table2id

[zjcxc]

下面是数据测试

--创建数据测试环境
declare @table1 table(id int identity(1,1),column11 int,column12 int)
insert into @table1
select 4,2
union all select 5,3

declare @table2 table(id int identity(1,1),column21 int,column22 int)
insert into @table2
select 1,2
union all select 2,3
union all select 3,5
union all select 4,7
union all select 5,8

declare @table3 table(id int identity(1,1),column31 int,column32 int)
insert into @table3
select 1,4
union all select 2,9
union all select 3,0

--查询结果
select id,table2id,column21,column22,TmpColumn=isnull(column12,column32)
from(
select id=(select count(*) from @table2 where id<=a.id),
table2id=a.id,a.column21,a.column22,b.column12,c.column32
from @table2 a
left join @table1 b on a.column21=b.column11
left join @table3 c on a.column21=c.column31
) a order by table2id




--/*--语句的执行结果
id table2id column21 column22 TmpColumn
----------- ----------- ----------- ----------- -----------
1 1 1 2 4
2 2 2 3 9
3 3 3 5 0
4 4 4 7 2
5 5 5 8 3

（所影响的行数为 5 行）
-------------------------------------------------------------------*/

[accounant]

select identity(int,1,1) as id,a.id as table2id,column21,column22,isnull(b.column12,c.column32) as column32
into #temp
from
table2 a full join table1 b on a.column21=b.column11
full join table3 c on a.column21=c.column31
select * from #temp

drop table #temp

[pengdali]

select (select sum(1) from table2 where id<=a.id) [id],a.*,b.column12 from table2 a left join (select column11,column12 from @table1 union all select column31,column32 from table3) b on a.column21=b.column11

就可以了。

[pengdali]

select (select sum(1) from table2 where id<=a.id) [id],a.*,b.column12 from table2 a left join (select column11,column12 from table1 union all select column31,column32 from table3) b on a.column21=b.column11

就可以了。

[pengdali]

测试：

declare @table1 table(id int identity(1,1),column11 int,column12 int)
insert into @table1
select 4,2
union all select 5,3

declare @table2 table(id int identity(1,1),column21 int,column22 int)
insert into @table2
select 1,2
union all select 2,3
union all select 3,5
union all select 4,7
union all select 5,8

declare @table3 table(id int identity(1,1),column31 int,column32 int)
insert into @table3
select 1,4
union all select 2,9
union all select 3,0


select (select sum(1) from @table2 where id<=a.id) [id],a.*,b.column12 from @table2 a left join (select column11,column12 from @table1 union all select column31,column32 from @table3) b on a.column21=b.column11

[hjb111]

select * into #t from table3
insert into #t(column31,column32) select column11,column12 from table2
select a.*,b.column32 btable from table2 a,#t b where a.id=b.id

[happydreamer]

select a.*,isnull(b.column12,c.column32)
from table2 a full join table1 b on a.column21=b.column11
full join table3 c on a.column21=c.column31

[yijiayi]

select a.*,isnull(b.column12,c.column32)
from table2 a full join table1 b on a.column21=b.column11
full join table3 c on a.column21=c.column31