求教一个sql语句
在oracle9i中
select   a.deviceitemobject   as   测量指标   ,   count(a.id)   as   一般缺陷
  from   checkresult   a  
  where   a.result=30
group   by   a.deviceitemobject

显示结果:
测量指标 严重缺陷
0 37
10 2062
20 2897

select   a.deviceitemobject   as   测量指标   ,   count(a.id)   as   一般缺陷
  from   checkresult   a  
  where   a.result=20
group   by   a.deviceitemobject

显示结果:
测量指标 一般缺陷  
0 12
10 3211
20 965

我想显示成

测量指标 一般缺陷   严重缺陷
0 12               37
10 3211           2062
20 965             2897

本人对oracle不太熟悉,求教sql语句怎么写.谢谢了先


------解决方案--------------------
select t1.clzb as "测量指标 ",t1.ybqx as "一般缺陷 ",t2.yzqx as "严重缺陷 "
from (
select a.deviceitemobject as clzb , count(a.id) as ybqx
from checkresult a
where a.result=20
group by a.deviceitemobject
)t1,
(
select a.deviceitemobject as clzb , count(a.id) as yzqx
from checkresult a
where a.result=30
group by a.deviceitemobject
)t2
where t1.clzb = t2.clzb;

------解决方案--------------------
select a.deviceitemobject as 测量指标 ,
sum(case when a.result=30 then 1 else 0) as 严重缺陷,
sum(case when a.result=20 then 1 else 0) as 一般缺陷
from checkresult a
group by a.deviceitemobject
------解决方案--------------------
对于2个结果集有部分交集的这种合并,最好是使用存储过程对临时表进行操作实现,使用MERGE
------解决方案--------------------
楼上的改一改,加个条件
select a.deviceitemobject as 测量指标 ,
sum(case when a.result=30 then 1 else 0) as 严重缺陷,
sum(case when a.result=20 then 1 else 0) as 一般缺陷
from checkresult a
WHERE A.RESULT IN (20,30)
group by a.deviceitemobject