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

[杨哥儿]

我在课程表（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

应该如何写语句。特向高手请教！

[mantisXF]

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         82

6 rows selected

[MJ_KC]

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

[tjinjie]

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

[杨哥儿]

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

[杨哥儿]

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';
但学生成绩表中多的课程会不显示。

[MJ_KC]

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中也可以.

[ShenLiang2025]

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 文学概论 2



eg：关于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 NULL

4）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

[ShenLiang2025]

真不好意思其实用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