A表:
id A1 A2 A3 A4
-----------------
1 2000 2001 2002 2003 B表
id name
-----------
2000 ‘aaa’
2001 ‘bbb’
2002 ‘ccc’
2003 ‘ddd’
现在我想用一个语句得到A表中id=1的A1,A2,A3,A4对应的B表中的name字段,请问怎么写?
id A1 A2 A3 A4
-----------------
1 2000 2001 2002 2003 B表
id name
-----------
2000 ‘aaa’
2001 ‘bbb’
2002 ‘ccc’
2003 ‘ddd’
现在我想用一个语句得到A表中id=1的A1,A2,A3,A4对应的B表中的name字段,请问怎么写?
inner join @TB B
on A.A1 = B.[ID] or A.A2 = B.[ID] or A.A3 = B.[ID] or A.A4 = B.[ID]
where A.[ID] = 1
select name from b where id = (select A1 from A where id = 1) or id = (select A2 from A where id = 1) or id = (select A3 from A where id = 1) or id = (select A4 from A where id = 1)
试试!
谢谢大家帮忙,你这个语句差不多实现了,不过当A.A1 = A.A2的时候,本来应该得到四个名字的,现在只得到三个,就是说,一样的名字就不行了。
select name from B where B.id in (select * from A where id=1)
各位兄弟的水平很高
学习。
1)如果是,则为以下情况(B表id不能重复)
A表:
id A1 A2 A3 A4
-----------------
1 2000 2000 2002 2003 B表
id name
-----------
2000 ‘aaa’
2001 ‘bbb’
2002 ‘ccc’
2003 ‘ddd’A表中不存在相应的2001,B表取到的id里肯定也没有2001,所以只有3条数据是正常的2)如果不是,则为以下情况(B表id可以重复)
id A1 A2 A3 A4
-----------------
1 2000 2000 2002 2003 B表
id name
-----------
2000 ‘aaa’
2000 ‘bbb’
2002 ‘ccc’
2003 ‘ddd’这样,取到4条数据应该是没有问题的,除非楼主的B表没有相应的数据,比如
B表
id name
-----------
2000 ‘aaa’
--2000 ‘bbb’这条数据不存在
2002 ‘ccc’
2003 ‘ddd’
不知道 jessenee()的那个一条的语句行不我用ADO
SELECT B.name as name1, B1.name AS name2,B2.name AS name3
FROM A INNER JOIN
B ON
A.A1 = B.id INNER JOIN
B B2 ON
A.A2 = B2.id INNER JOIN
B B3 ON
A.A3 = B3.id