包含表间和表内查询的问题

[sqlbeginner888]

有两个表

表1(两列)

id A(唯一约束)
1 a
2 b
3 c
4 d
5 e

表2(多列；除V, Condition(bit)列外，其他列都可以有null值-这里我用n来代表null；表中V到Z列中的数据与表1种的id关联)

id V W X Y Z Condition
1 1 n 3 3 5 true
2 3 5 1 2 n False
3 2 2 4 4 n False
4 5 n 1 3 2 True
5 2 n 2 5 4 False

问题如下：
如何找出所有包含c的表2中的记录(第1,2,4三条记录)，当Condition列为true时，一并显示字段名Condition，显示效果如下

V:a W:null X:c Y:c Z:e Condition
V:c W:e X:a Y:b Z:null
V:e W:null X:a Y:c Z:b Condition

(谢过各位，感谢帮忙)

[marco08]

create table T1(id int, A varchar(10))
insert T1 select 1, 'a'
union all select 2, 'b'
union all select 3, 'c'
union all select 4, 'd'
union all select 5, 'e'

create table T2(id int, V int, W int, X int, Y int, Z int, Condition bit)
insert T2 select 1, 1, null, 3, 3, 5, 1
union all select 2, 3, 5, 1, 2, null, 0
union all select 3, 2, 2, 4, 4, null, 0
union all select 4, 5, null, 1, 3, 2, 1
union all select 5, 2, null, 2, 5, 4, 0


select
V=(select A from T1 where id=tmp.V),
W=(select A from T1 where id=tmp.W),
X=(select A from T1 where id=tmp.X),
Y=(select A from T1 where id=tmp.Y),
Z=(select A from T1 where id=tmp.Z)+(case when tmp.Condition=1 then ' Condition' else '' end)
from T2 as tmp
inner join T1 on T1.A='c' and (tmp.V=T1.id or tmp.W=T1.id or tmp.X=T1.id or tmp.Y=T1.id or tmp.Z=T1.id)

--result
V W X Y Z
---------- ---------- ---------- ---------- --------------------
a NULL c c e Condition
c e a b NULL
e NULL a c b Condition

(3 row(s) affected)

[sqlbeginner888]

可能我表达得不好，还望见谅，我的总体意思是当客户给我“c”，我就根据这两个表(我考虑过不要condition了，麻烦)，得出

V W X Y Z
---------- ---------- ---------- ---------- --------------------
a NULL c c e
c e a b NULL
e NULL a c b

给我“b”，我同样根据这两个表，得出

V W X Y Z
---------- --------- ----------- ---------- --------------------
c e a b null
b b d d null
e null a c b
b null b e d

感谢楼上大虾，我这两个表都很大，我是初学的，可能不明你的方法，非常感谢你的解答

[marco08]

create table T1(id int, A varchar(10))
insert T1 select 1, 'a'
union all select 2, 'b'
union all select 3, 'c'
union all select 4, 'd'
union all select 5, 'e'

create table T2(id int, V int, W int, X int, Y int, Z int, Condition bit)
insert T2 select 1, 1, null, 3, 3, 5, 1
union all select 2, 3, 5, 1, 2, null, 0
union all select 3, 2, 2, 4, 4, null, 0
union all select 4, 5, null, 1, 3, 2, 1
union all select 5, 2, null, 2, 5, 4, 0


select
V=(select A from T1 where id=tmp.V),
W=(select A from T1 where id=tmp.W),
X=(select A from T1 where id=tmp.X),
Y=(select A from T1 where id=tmp.Y),
Z=(select A from T1 where id=tmp.Z)
from T2 as tmp
inner join T1 on T1.A='c' --把c改成b就可以了
and (tmp.V=T1.id or tmp.W=T1.id or tmp.X=T1.id or tmp.Y=T1.id or tmp.Z=T1.id)

--result
V W X Y Z
---------- ---------- ---------- ---------- ----------
a NULL c c e
c e a b NULL
e NULL a c b

(3 row(s) affected)

[sqlbeginner888]

再次表示不解，望楼上大虾见谅，我的表1，一共有十万条记录，请问按你的方法
从insert T1 select 1, 'a' 开始
我将要打
union all select 2, 'b'
union all select 3, 'c'
union all select 4, 'd'
union all select 5, 'e'
一直打到
union all seletc 100,000, 'character 100000'，岂非累死我

本人愚钝，还望楼上大虾莫见怪

[dawugui]

你的内容是应该已经存在的,不需要输入.只需要使用下面的语句即可.

select
V=(select A from T1 where id=tmp.V),
W=(select A from T1 where id=tmp.W),
X=(select A from T1 where id=tmp.X),
Y=(select A from T1 where id=tmp.Y),
Z=(select A from T1 where id=tmp.Z)
from T2 as tmp
inner join T1 on T1.A='c' --把c改成b就可以了
and (tmp.V=T1.id or tmp.W=T1.id or tmp.X=T1.id or tmp.Y=T1.id or tmp.Z=T1.id)

[sqlbeginner888]

谢谢楼上大虾，测试成功，非常感谢