SELECT c.customer_id, c.o.order_id, c.account_mgr_id, o.sales_rep_id FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id ORDER BY c.customer_id;
full outer join 好像是SQL Server才可以用的吧?
a full out join b on ... ---> Return all rows from the a table and all rows from the b table. Rows that do not satisfy the ON conditionare extended with nulls.
in oracle9i,you can use full out join
也许oracle 9i支持这种写法吧。
SQL> SELECT ename||job AS "Employees" 2 FROM emp;
我用的是oracle 8i怎么办?难道就没有办法了吗?oracle就这么弱吗?
左连接 union 右连接,不就可以了。
select A.*,B,* from a,B where a.f = b.f(+) union ALL select A.*,B,* from a,B where a.f(+) = b.f
用enhydraboy(乱舞的浮尘):的方法无觉得好, select A.*,B.* from a,B where a.f = b.f(+) union ALL select A.*,B.* from a,B where a.f(+) = b.f;
select * from (( select kh.sm,kh.cnt,xh.cnt from (select sm,count(*) cnt from tbm_yd,tyt_yhda where dm=yd and to_char(khsj,'yyyymmdd') = '20030825' group by sm) kh, (select sm,count(*) cnt from tbm_yd,tyt_xhyd where dm=yd and to_char(xhsj,'yyyymmdd') = '20030825' group by sm) xh where kh.sm=xh.sm(+) ) union all ( select kh.sm,kh.cnt,xh.cnt from (select sm,count(*) cnt from tbm_yd,tyt_yhda where dm=yd and to_char(khsj,'yyyymmdd') = '20030825' group by sm) kh, (select sm,count(*) cnt from tbm_yd,tyt_xhyd where dm=yd and to_char(xhsj,'yyyymmdd') = '20030825' group by sm) xh where kh.sm(+)=xh.sm ))ORA-00918: column ambiguously defined 为什么?
SELECT c.customer_id, c.o.order_id, c.account_mgr_id, o.sales_rep_id
FROM customers c FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
Return all rows from the a table and all rows from the b table.
Rows that do not satisfy the ON conditionare extended with nulls.
SQL> SELECT ename||job AS "Employees"
2 FROM emp;
union ALL
select A.*,B,* from a,B where a.f(+) = b.f
select A.*,B.* from a,B where a.f = b.f(+)
union ALL
select A.*,B.* from a,B where a.f(+) = b.f;
((
select kh.sm,kh.cnt,xh.cnt
from
(select sm,count(*) cnt from tbm_yd,tyt_yhda where dm=yd and to_char(khsj,'yyyymmdd') = '20030825' group by sm) kh,
(select sm,count(*) cnt from tbm_yd,tyt_xhyd where dm=yd and to_char(xhsj,'yyyymmdd') = '20030825' group by sm) xh
where kh.sm=xh.sm(+)
)
union all
(
select kh.sm,kh.cnt,xh.cnt
from
(select sm,count(*) cnt from tbm_yd,tyt_yhda where dm=yd and to_char(khsj,'yyyymmdd') = '20030825' group by sm) kh,
(select sm,count(*) cnt from tbm_yd,tyt_xhyd where dm=yd and to_char(xhsj,'yyyymmdd') = '20030825' group by sm) xh
where kh.sm(+)=xh.sm
))ORA-00918: column ambiguously defined
为什么?
仔细检查你里面的连接部分的select语句,肯定没有在同名的列前,加上表名。"除了用union还有其他办法吗?弱oracle!!",方法有,只不过你没有耐性去解决你的语句bug。