日期:2014-05-17  浏览次数:20629 次

请教sql高手
select   x.*.y.*   from   (select   a,b   from   t)   x,(select   a,b   from   t2)y
按照上面的写法,当y表中无记录x表中有记录   或   y表中有记录x表中无记录时,整个语句不返回值,也就是说只要其中一个表无记录整个语句就不返回值。

现在的想只要x表或y表其中任何表存在记录时,就要显示字段出来,语句该怎么写?

------解决方案--------------------
照你的写法,只要有任何纪录,就会显示的啊,而且数据重复和会很多噢
------解决方案--------------------
用两句比较容易解决
select x.* .y.*
from (select a, b from t) x
left join (select a, b from t2) y on x.a = y.a
where (select count(*) from (select a, b from t)) >
(select count(*) from select a, b from t2
));

select x.* .y.*
from (select a, b from t) x
right join (select a, b from t2) y on x.a = y.a
where (select count(*) from (select a, b from t)) <
(select count(*) from select a, b from t2
));
------解决方案--------------------
用full outer join on 1=1来解决。
------解决方案--------------------
SQL> select * from t;

A B
-------------------- --------------------
a b

SQL> select * from t2;

未选定行

SQL> select x.*,y.* from
2 (select a,b from t) x full outer join
3 (select a,b from t2) y on 1=1;

A B A
-------------------- -------------------- --------------------
B
--------------------
a b