两个表查询

[_VINCE_]

表A结构 a1,a2,a3
表B结构id,b1

表A的a1,2,3都是数字，对于表B的id，所以要查询表A，把a1,a2,a3 对应到表b的id的b1数据

这应该如何写查询？

[---涛声依旧---]

select t1.b1 from B t1
where exists(select 1 from A t2 where t1.id=t2.a1 or t1.id=t2.a2 or t1.b1=t2.a3)

[renliquan]

--测试数据 create table A (a1 int,a2 int,a3 int) insert into A select 1,4,3 UNION ALL select 2,2,4 UNION ALL select 3,1,1 UNION ALL select 4,2,3 UNION ALL select 3,1,1 UNION ALL select 2,2,3 CREATE TABLE B (id int,b1 nvarchar(10)) INSERT INTO B SELECT 1,'AAA' UNION ALL SELECT 2,'BBB' UNION ALL SELECT 3,'CCC' UNION ALL SELECT 4,'DDD' --语句 SELECT a1,(SELECT b1 FROM B WHERE id=a1) AS a1b1,a2,(SELECT b1 FROM B WHERE id=a2) AS a2b1,a3,(SELECT b1 FROM B WHERE id=a3) AS a3b1 FROM A --结果 a1 a1b1 a2 a2b1 a3 a3b1 ----------- ---------- ----------- ---------- ----------- ---------- 1 AAA 4 DDD 3 CCC 2 BBB 2 BBB 4 DDD 3 CCC 1 AAA 1 AAA 4 DDD 2 BBB 3 CCC 3 CCC 1 AAA 1 AAA 2 BBB 2 BBB 3 CCC

[卖水果的net]

比较常用的方法就是 2# 的。 LZ 可以理解成 3 个 left join 查询。