有两个表表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(谢过各位,感谢帮忙)
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(谢过各位,感谢帮忙)
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)
---------- ---------- ---------- ---------- --------------------
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感谢楼上大虾,我这两个表都很大,我是初学的,可能不明你的方法,非常感谢你的解答
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)
从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',岂非累死我本人愚钝,还望楼上大虾莫见怪
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)