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'
修正: 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'是不是要这样的结果!
--这个意思吗?select * from 表1 a1 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)
--如果是上面的意思,再试试下面的写法:select a.* 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
b1,c1,d1,a3,a4字段均要建立索引.
select a.* from a,b,c,d where d.d1='1' and b.b1=d.b1 and c.c1=d.c1 and a.a3=b.a3 and a.a4=c.a4 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没测试,不知对不对
建议的索引,b1,c1 a3,a4建立复合索引.
当然,d1,b1,c1也可以建立复合索引,看看效率是否会更高
谢谢各位捧场 我的意思是 表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
t=d.b1 and t1=d.c1看不懂,能否举例说明?
declare @dd varchar(10) 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
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