select a.* from 表1 a join 表4 b on a.a3=b.b1 and a.a4=b.c1 and b.dl='1'
union
select a.* from [表1-1] c join 表4 d on c.a3=d.b1 and c.a4=d.c1 and d.dl='1'
union
select a.* from [表1-1] c join 表4 d on c.a3=d.b1 and c.a4=d.c1 and d.dl='1'
select a.* from 表1 a join 表4 b on a.a3=b.b1 and a.a4=b.c1 and b.dl='1'
union
select c.* from [表1-1] c join 表4 d on c.a3=d.b1 and c.a4=d.c1 and d.dl='1'是不是要这样的结果!
所有代表什么意思?
where exists(select 1 from 表4 where d1='1' and a1.a3=b1 and a1.a4=c1)
union all
select * from [表1-1] a2
where exists(select 1 from 表4 where d1='1' and a2.a3=b1 and a2.a4=c1)
from(
select * from 表1
union all
select * from 表2
)a join (
select b1,c1 from 表4 where d1='1' group by b1,c1
)b on a.a3=b.b1 and a.a4=b.c1
union
select a1.* from a1,b,c,d where d.d1='1' and b.b1=d.b1 and c.c1=d.c1 and a1.a3=b.a3 and a1.a4=c.a4没测试,不知对不对
我的意思是
表1:a(a1,a2,a3,a4)和表2、表3关联起来
select a.a1,a.a2,a.a3,a.a4,b.b1 as t ,c.c1 as t1 from a
left outer join b on b.a3=a.a3
left outerjoin c on c.a4=a.a4
where (t=d.b1 and t1=d.c1) or (t=d.b2 and t1=d.c2 ) or...or (t=d.bn and t1=d.cn )
b1-bn ,c1-cn 为表4中的对应值
表2:b(b1,b2,a3)
表3:c(c1,c2,a4)
表4:d(d1,d2,b1,c1)
查询:将表1和表1-1中的所有记录查询出来 ,并且a3,a4 的值等于表4中的d1='1' 的所有b1、c1
set @dd='1'select a.*
from(
select * from a
union all
select * from a1
)a join(
select b.a3,c.a4
from d,b,c
where d.d1=@dd
and d.b1=b.b1
and d.c1=c.c1
group by b.a3,c.a4
)b on a.a3=b.a3 and a.a4=b.a4