请教:关于多表数据合并的问题 本帖最后由 xrzs1986 于 2011-05-21 17:01:41 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 --如果只想在sqlplus中显示,只需要设置环境变量null为"-"就行了SQL> set null "-"SQL> set linesize 200SQL> SELECT coalesce(a.deptno, b.deptno, c.deptno) deptno, 2 a.dname, 3 a.age, 4 b.dname1, 5 b.loc1, 6 c.dname2, 7 c.loc2 8 FROM dept a 9 FULL OUTER JOIN dept1 b 10 ON (a.deptno = b.deptno) 11 FULL OUTER JOIN dept2 c 12 ON (c.deptno = b.deptno); DEPTNO DNAME AGE DNAME1 LOC1 DNAME2 LOC2---------- ---------- ---------- ---------- -------- ---------- -------- 10 ACCOUNTING 12 ACCOUNTING NEW YORK ACCOUNTING NEW YORK 20 RESEARCH 23 RESEARCH DALLAS RESEARCH DALLAS 30 SALES 34 SALES CHICAGO SALES CHICAGO 40 OPERATIONS 56 - - - - 50 - - - - OPERATIONS BOSTONSQL> --手工转换SELECT coalesce(a.deptno, b.deptno, c.deptno) deptno, nvl(a.dname, '-') dname, nvl(to_char(a.age), '-') age, nvl(b.dname1, '-') dname1, nvl(b.loc1, '-') loc1, nvl(c.dname2, '-') dname2, nvl(c.loc2, '-') loc2 FROM dept a FULL OUTER JOIN dept1 b ON (a.deptno = b.deptno) FULL OUTER JOIN dept2 c ON (c.deptno = b.deptno); 多谢 tangren 兄的解答,1、合并用到了coalesce 函数,刚搞oracle不久,对这些内置函数的用法还理解不深,之前我还在想怎么group by deptno,现在看来想偏了2、age在nvl中的数据类型不匹配的问题用to_char转换下就行了当时搞晕了,没想到 - _ -3、后面给出的环境变量的方法也不失为一个好方法,学习了! thx~ 请教关于DBWR进程的几个问题 ORACLE 初级问题 通过db_link如何通过convert函数修改Oracle字符集 求Oracle课程设计,不用太复杂的 Oracle高手请进,300分!一个oracle9i回退段的问题. 高分求助oralce 10g正则表达式的问题~ 达人帮忙啊 执行SQL语句出错(报ORA-00922:缺少或无效选项) oracle数据导入问题 在存储过程中求和问题。 怎么不能删除了 请问有介绍使用"PLSQL Developer"操作oracle 10g的书吗? 菜鸟刚进公司拖了大家的后腿,包体存储过程表达式类型错误和statement ignored
SQL> set null "-"
SQL> set linesize 200
SQL> SELECT coalesce(a.deptno, b.deptno, c.deptno) deptno,
2 a.dname,
3 a.age,
4 b.dname1,
5 b.loc1,
6 c.dname2,
7 c.loc2
8 FROM dept a
9 FULL OUTER JOIN dept1 b
10 ON (a.deptno = b.deptno)
11 FULL OUTER JOIN dept2 c
12 ON (c.deptno = b.deptno); DEPTNO DNAME AGE DNAME1 LOC1 DNAME2 LOC2
---------- ---------- ---------- ---------- -------- ---------- --------
10 ACCOUNTING 12 ACCOUNTING NEW YORK ACCOUNTING NEW YORK
20 RESEARCH 23 RESEARCH DALLAS RESEARCH DALLAS
30 SALES 34 SALES CHICAGO SALES CHICAGO
40 OPERATIONS 56 - - - -
50 - - - - OPERATIONS BOSTONSQL> --手工转换
SELECT coalesce(a.deptno, b.deptno, c.deptno) deptno,
nvl(a.dname, '-') dname,
nvl(to_char(a.age), '-') age,
nvl(b.dname1, '-') dname1,
nvl(b.loc1, '-') loc1,
nvl(c.dname2, '-') dname2,
nvl(c.loc2, '-') loc2
FROM dept a
FULL OUTER JOIN dept1 b
ON (a.deptno = b.deptno)
FULL OUTER JOIN dept2 c
ON (c.deptno = b.deptno);
1、合并用到了coalesce 函数,刚搞oracle不久,对这些内置函数的用法还理解不深,之前我还在想怎么group by deptno,现在看来想偏了
2、age在nvl中的数据类型不匹配的问题用to_char转换下就行了当时搞晕了,没想到 - _ -
3、后面给出的环境变量的方法也不失为一个好方法,学习了!
thx~