Select a.f1,a.f2,b.f1,b.f2 form a,b where b.f2 = 1 AND a.f1 = B.f1
select * from a left join b on a.f1=b.f1 where b.f2=1
select a.* from a,b where a.f1 = b.f1 and b.f2 = 1 union all select * from a where not exists(select 1 from b where a.f1 = f1)
select * from a left join b on a.f1=b.f1 where b.f2=1 union select * from a inner join b on a.f1 not in(select b.f1 from b)
to happyflyston union这个我是知道的 不过是想知道sql SERVER 有没有办法通过一条语句产生出来呢 不用UNION
上面的错了:select * from a left join b on a.f1=b.f1 where b.f2=1 union select * from a where a.f1 not in(select b.f1 from b)
谢谢 大家看来只能UNION了
select * from (select * from a left join b on a.f1=b.f1) t where b.f2=1 or b.f2 is null
晕了,今天怎么老出错这样可以 select * from (select a.f1 as af1,a.f2 as af2,b.f1 as bf1,b.f2 as bf2 from a left join b on a.f1=b.f1) t where t.bf2=1 or t.bf2 is null
select * from a left join b on a.f1=b.f1 where b.f2=1 union select * from a where a.f1 not in(select b.f1 from b)
a (F1,F2),b (f1,f2)中取出 所有a中所有b.f2 = 1 AND a.f1 = B.f1 或者 a.f1在B.F1中没有对应数据的记录select A.* from A where exists (select 1 from B where B.f2 = 1 AND A.f1 = A.f1) or (A.F1 not in (select F1 from B))
--貌似这样更合适些 select * from (select a.f1 as af1,a.f2 as af2,b.f1 as bf1,b.f2 as bf2 from a left join b on a.f1=b.f1) t where t.bf2=1 or (t.bf1 is null and t.bf2 is null)
select *
from a left join b
on a.f1=b.f1
where b.f2=1
from a,b
where a.f1 = b.f1 and b.f2 = 1
union all
select *
from a
where not exists(select 1 from b where a.f1 = f1)
from a left join b
on a.f1=b.f1
where b.f2=1
union
select *
from a inner join b
on a.f1 not in(select b.f1 from b)
from a left join b
on a.f1=b.f1
where b.f2=1
union
select *
from a
where a.f1 not in(select b.f1 from b)
from (select *
from a left join b
on a.f1=b.f1) t
where b.f2=1 or b.f2 is null
select *
from (select a.f1 as af1,a.f2 as af2,b.f1 as bf1,b.f2 as bf2
from a left join b
on a.f1=b.f1) t
where t.bf2=1 or t.bf2 is null
from a left join b
on a.f1=b.f1
where b.f2=1
union
select *
from a
where a.f1 not in(select b.f1 from b)
exists (select 1 from B where B.f2 = 1 AND A.f1 = A.f1)
or (A.F1 not in (select F1 from B))
select *
from (select a.f1 as af1,a.f2 as af2,b.f1 as bf1,b.f2 as bf2
from a left join b
on a.f1=b.f1) t
where t.bf2=1 or (t.bf1 is null and t.bf2 is null)