有三个表A,B,C,
当A.Field1=0时, 关联B表的内容,
当A.Field1=1时, 关联C表的内容,
显示在一条查询语句里,怎么来写呢?如A:
Field1 Field2
0 001
1 002B
F1 F2
001 hhhC
F1 F3
002 yyy希望显示出来Field1 Field2 F2 F3
0 001 hhh (null)
1 002 (null) yyy
当A.Field1=0时, 关联B表的内容,
当A.Field1=1时, 关联C表的内容,
显示在一条查询语句里,怎么来写呢?如A:
Field1 Field2
0 001
1 002B
F1 F2
001 hhhC
F1 F3
002 yyy希望显示出来Field1 Field2 F2 F3
0 001 hhh (null)
1 002 (null) yyy
Field1,
Field2,
F2=(case Field1 when 0 then (select F2 from B where F1=A.Field2) end),
F3=(case Field1 when 1 then (select F3 from C where F1=A.Field2) end)
from
A
join b on
a.field1 = b.f1 and a.field1=0
join c
on a.field1=b.f1 and a.field1=1
insert into @A select 0,'001'
insert into @A select 1,'002'declare @B table(F1 varchar(4),F2 varchar(4))
insert into @B select '001','hhh'declare @C table(F1 varchar(4),F3 varchar(4))
insert into @C select '002','yyy'select
a.Field1,
a.Field2,
F2=(case Field1 when 0 then (select F2 from @B where F1=A.Field2) end),
F3=(case Field1 when 1 then (select F3 from @C where F1=A.Field2) end)
from
@A a /*
Field1 Field2 F2 F3
----------- ------ ---- ----
0 001 hhh NULL
1 002 NULL yyy
*/
insert into @A select 0,'001'
insert into @A select 1,'002'declare @B table(F1 varchar(4),F2 varchar(4))
insert into @B select '001','hhh'declare @C table(F1 varchar(4),F3 varchar(4))
insert into @C select '002','yyy'select
a.Field1,
a.Field2,
F2=(case Field1 when 0 then (select top 1 F2 from @B where F1=A.Field2) end),
F3=(case Field1 when 1 then (select top 1 F3 from @C where F1=A.Field2) end)
from
@A a /*
Field1 Field2 F2 F3
----------- ------ ---- ----
0 001 hhh NULL
1 002 NULL yyy
*/
insert into #t1(field1,field2) values('0', '001')
insert into #t1(field1,field2) values('1', '002') create table #t2(F1 varchar(10) , F2 varchar(10))
insert into #t2(f1,f2) values('001', 'hhh') create table #t3(F1 varchar(10) , F3 varchar(10))
insert into #t3(f1,f3) values('002', 'yyy') select a.* , isnull(#t3.f3 , null) as f3 from
(
select field1 , field2 , isnull(#t2.f2 , null) as f2
from #t1 left join #t2 on #t1.field2 = #t2.f1
) a
left join #t3 on a.field2 = #t3.f1drop table #t1
drop table #t2
drop table #t3field1 field2 f2 f3
---------- ---------- ---------- ----------
0 001 hhh NULL
1 002 NULL yyy(所影响的行数为 2 行)
declare @A table(Field1 int,Field2 varchar(4))
insert into @A select 1,'001'
insert into @A select 2,'002'
insert into @A select 3,'003'
insert into @A select 4,'004'declare @B table(F1 varchar(4),F2 varchar(4))
insert into @B select '001','111'
insert into @B select '002','222'declare @C table(F1 varchar(4),F3 varchar(4))
insert into @C select '003','333'
insert into @C select '004','444'select
a.Field1,
a.Field2,
F2=(case Field1 when 0 then (select top 1 F2 from @B where F1=A.Field2) end),
F3=(case Field1 when 1 then (select top 1 F3 from @C where F1=A.Field2) end)
from
@A a Field1 Field2 F2 F3
----------- ------ ---- ----
1 001 NULL NULL
2 002 NULL NULL
3 003 NULL NULL
4 004 NULL NULL(所影响的行数为 4 行)
我的方法是正确的
create table #t1(Field1 varchar(10) , Field2 varchar(10))
insert into #t1(field1,field2) values('1', '001')
insert into #t1(field1,field2) values('2', '002')
insert into #t1(field1,field2) values('3', '003')
insert into #t1(field1,field2) values('4', '004')
select * from #t1create table #t2(F1 varchar(10) , F2 varchar(10))
insert into #t2(f1,f2) values('001', '111')
insert into #t2(f1,f2) values('002', '222')
select * from #t2create table #t3(F1 varchar(10) , F3 varchar(10))
insert into #t3(f1,f3) values('003', '333')
insert into #t3(f1,f3) values('004', '444')
select * from #t3select a.* , isnull(#t3.f3 , null) as f3 from
(
select field1 , field2 , isnull(#t2.f2 , null) as f2
from #t1 left join #t2 on #t1.field2 = #t2.f1
) a
left join #t3 on a.field2 = #t3.f1drop table #t1
drop table #t2
drop table #t3field1 field2 f2 f3
---------- ---------- ---------- ----------
1 001 111 NULL
2 002 222 NULL
3 003 NULL 333
4 004 NULL 444(所影响的行数为 4 行)