select a.id,b.id1,c.id1,a.field1,a.field2 from TableA a,TableB b,TableC c where a.id=b.id and b.id=c.id
select a.id,b.id1,c.id1,a.field1,a.field2 from a,b,c where a.id=b.id and b.id=c.id
先谢谢大家的 回答. 但回答全错的.大家的 写法.有null的查询不出的
SELECT case WHEN c.id IS null THEN d.id ELSE c.id END AS id ,c.id1 AS Cidl,d.Bidl,d.f1,d.f2 FROM tblC c FULL JOIN ( SELECT case WHEN a.id IS null THEN b.id ELSE a.id END AS id ,b.id1 AS Bidl,a.f1,a.f2 FROM tblA a FULL JOIN tblB b ON a.id=b.id ) d ON c.id =d.id
1 11 11 XXX XXXX 1 12 12 XXX XXXX 1 13 NULL XXX XXXX 2 21 NULL XXX XXXX 2 22 22 XXX XXXX 2 NULL 24 XXX XXXX 3 33 NULL XXX XXXX 4 NULL 42 XXX XXXX 4 NULL 56 XXX XXXXselect tid,case when exists (select ttid from #tmp2 where ttid=c.ttid) then c.ttid end as ttid2 ,case when exists (select ttid from #tmp3 where ttid=c.ttid) then c.ttid end as ttid3 ,field1,field2 from ( select distinct a.tid,a.ttid,b.field1,b.field2 from ( select * from #tmp2 union select * from #tmp3 ) a,#tmp1 b where a.tid=b.tid or (not exists (select tid from #tmp1 where tid=a.tid)) ) c跟高效率的语句,摸索中!
from TableA a,TableB b,TableC c
where a.id=b.id and b.id=c.id
from a,b,c
where a.id=b.id and b.id=c.id
先谢谢大家的 回答.
但回答全错的.大家的 写法.有null的查询不出的
SELECT case WHEN a.id IS null THEN b.id ELSE a.id END AS id ,b.id1 AS Bidl,a.f1,a.f2 FROM tblA a FULL JOIN tblB b ON a.id=b.id
) d ON c.id =d.id
1 12 12 XXX XXXX
1 13 NULL XXX XXXX
2 21 NULL XXX XXXX
2 22 22 XXX XXXX
2 NULL 24 XXX XXXX
3 33 NULL XXX XXXX
4 NULL 42 XXX XXXX
4 NULL 56 XXX XXXXselect tid,case when exists (select ttid from #tmp2 where ttid=c.ttid) then c.ttid end as ttid2
,case when exists (select ttid from #tmp3 where ttid=c.ttid) then c.ttid end as ttid3
,field1,field2 from
(
select distinct a.tid,a.ttid,b.field1,b.field2 from
(
select * from #tmp2
union
select * from #tmp3
) a,#tmp1 b
where a.tid=b.tid or (not exists (select tid from #tmp1 where tid=a.tid))
) c跟高效率的语句,摸索中!
01 11 11 XXXX XXXX
01 12 12 XXXx XXXX
而不是
A.id(=B.id=c.id). B.id1 C.id1 field1 field2
01 11 12 XXXX XXXX
01 12 11 XXXx XXXX是不是B.id1必须等于C.id1,或是另有一个字段决定
from a inner join b on a.id=b.b-id inner join on a.id=c.c-id