在oracle中使用left join达不到预期结果

Question

我在课程表（kcb）和成绩表(cjb)查询某生的成绩情况，课程表中有的，成绩表中没有也要显示。用left join只能显示交集，不能显示并集，为何？
查询课程表：
select bjmc,kcmc,xf from kcb where bjmc='中文0601';
结果：
中文0601 古代文学 3
中文0601 现代文学 3
中文0601 文学概论 2
中文0601 修辞     4
中文0601 写作     4
查询成绩表：
select bjmc,kcmc,xf,zf from cjb where xm='位东方';
结果：
中文0601 古代文学 3 68
中文0601 现代文学 3 82
中文0601 修辞     4 79
中文0601 写作     4 84
中文0601 现代汉语 3 90用left join 查询：
select a.bjmc,a.kcmc,a.xf,b.zf from kcb a left join cjb b on a.kcmc=b.kcmc where a.bjmc='中文0601' and b.xm='位东方';
结果：
中文0601 古代文学 3 68
中文0601 现代文学 3 82
中文0601 修辞     4 79
中文0601 写作     4 84而我要的是：
结果：
中文0601 古代文学 3 68
中文0601 现代文学 3 82
中文0601 文学概论 2
中文0601 修辞     4 79
中文0601 写作     4 84
中文0601 现代汉语 3 90应该如何写语句。特向高手请教！

Answer 1

try it ...
select bjmc,
       kcmc,
       xf,
       max(zf) as zf
  from (           
        select t1.*,
               null as zf
          from (        
                select bjmc,kcmc,xf from kcb where bjmc= '中文0601'
               )t1
        union 
        select *
          from (            
                select bjmc,kcmc,xf,zf from cjb where xm= '位东方'
               )t2
        )tt
 group by bjmc,kcmc,xf;
BJMC     KCMC             XF         ZF
-------- -------- ---------- ----------
中文0601 写作              4         84
中文0601 修辞              4         79
中文0601 古代文学          3         68
中文0601 文学概论          2 
中文0601 现代汉语          3         90
中文0601 现代文学          3         826 rows selected

Answer 2

select max(bjmc),kcmc,max(xf),max(zf) from
(
select bjmc,kcmc,xf,zf from  cjb 
union
select bjmc,kcmc,xf,null from  kcb
) a
group by kcmc

Answer 3

谢谢楼上。可我在oracle9i中不能实现上述结果，为何？

Answer 4

mantisXF：
您用的是SQLSERVER吧。as 别名在oracle中没有用过。
我用你的思路改好后可以达到结果，但速度太慢。
我要统计10000多学生呀。我用这个语句可以解决：
select a.bjmc,a.kcmc,a.xf,
(select b.zf from cjb b where b.xm= '位东方 and a.kcmc=b.kcmc) zf
from kcb a where a.bjmc= '中文0601';
但学生成绩表中多的课程会不显示。

Answer 5

SELECT a.bjmc, a.kcmc, a.xf, b.zf
  FROM (SELECT bjmc, kcmc, xf
          FROM kcb
         WHERE bjmc = '中文0601 ') a
       FULL OUTER JOIN
       (SELECT bjmc, kcmc, xf, zf
          FROM cjb
         WHERE xm = '位东方 ') b ON a.kcmc = b.kcmc

Answer 6

select   max(bjmc),kcmc,max(xf),max(zf)   from 
( 
select   bjmc,kcmc,xf,zf   from     cjb   where xm= '位东方'
union 
select   bjmc,kcmc,xf,null   from     kcb  where   bjmc= '中文0601'
)   a 
group   by   kcmc
MAX(BJMC)  KCMC          MAX(XF)    MAX(ZF)
---------- ---------- ---------- ----------
中文0601   古代文学            3         68
中文0601   文学概论            2
中文0601   现代汉语            3         90
中文0601   现代文学            3         82
中文0601   写作                4         84
中文0601   修辞                4         79
PS:   as   别名 这种 在oracle中也可以.

Answer 7

select a.*,b.zf from kcb a full join cjb b
on a.bjmc=b.bjmc
and a.kcmc=b.kcmc
and b.bjmc='中文0601'
and b.xm='位东方';
/
执行结果如下：BJMC         KCMC               XF         ZF
------------ ---------- ---------- ----------
中文0601     古代文学            3         68
中文0601     现代文学            3         82
中文0601     修辞                4         79
中文0601     写作                4         84
中文0601     文学概论            2
                                           90
显然不是符合要求的，所以用join是不可以解决问题的 要不你再试试~~
可行的方法 如上面的回复（在oracle里）：select   max(bjmc),kcmc,max(xf),max(zf)   from 
( 
select   bjmc,kcmc,xf,zf   from     cjb   where xm= '位东方'
union 
select   bjmc,kcmc,xf,null   from     kcb  where   bjmc= '中文0601'
)   a 
group   by   kcmc;
/MAX(BJMC)    KCMC          MAX(XF)    MAX(ZF)
------------ ---------- ---------- ----------
中文0601     古代文学            3         68
中文0601     现代文学            3         82
中文0601     写作                4         84
中文0601     修辞                4         79
中文0601     现代汉语            3         90
中文0601     文学概论            2eg：关于join的（server2005里的 oracle里结果一样）...create table testfulljoin
(a char(4),
b char(4),
c char(4))
insert  testfulljoin(a,b,c)
select 'a','b','c' union all
select 'b','a','d' union all
select 'c','d','e' union all
select 'd','f','g' 
create table testfulljoin2
(b char(4),
c char(4),
d char(4))
insert testfulljoin2(b,c,d)
select 'b','c','d' union all
select 'd','e','g' union all
select 'f','d','g' union all
select 'd','e','c' 1）right join
select a.* ,b.d from testfulljoin2 as b right join testfulljoin as a on a.b=b.b and a.c=b.c;
a    b    c    d   
b    a    d    NULL
c    d    e    g   
c    d    e    c   
d    f    g    NULL
2）left join
select a.* ,b.d from testfulljoin2 as b left join testfulljoin as a on a.b=b.b and a.c=b.c;
a    b    c    d   
c    d    e    g   
NULL NULL NULL g   
c    d    e    c   3）full join
select a.* ,b.d from testfulljoin2 as b full join testfulljoin as a on a.b=b.b and a.c=b.c;
a    b    c    d   
c    d    e    g   
NULL NULL NULL g   
c    d    e    c   
b    a    d    NULL
d    f    g    NULL4）inner join
select a.* ,b.d from testfulljoin2 as b inner join testfulljoin as a on a.b=b.b and a.c=b.c;
a    b    c    d   
c    d    e    g   
c    d    e    c   

Answer 8

 真不好意思其实用full join是可以做到的！
 select nvl(a.kcmc,b.kcmc),nvl(a.xf,b.xf),nvl(a.bjmc,b.bjmc),b.zf from kcb a
 full join cjb b
  on a.bjmc=b.bjmc
  and a.kcmc=b.kcmc
  and b.bjmc='中文0601'
  and b.name='位东方'; 
我没用别名 结果如下：
NVL(A.KCMC NVL(A.XF,B.XF) NVL(A.BJMC,B         ZF
---------- -------------- ------------ ----------
古代文学                3 中文0601             68
现代文学                3 中文0601             82
修辞                    4 中文0601             79
写作                    4 中文0601             84
文学概论                2 中文0601
现代汉语                3 中文0601             90