求教一个sql语句
在oracle9i中
select a.deviceitemobject as 测量指标 , count(a.id) as 一般缺陷
from checkresult a
where a.result=30
group by a.deviceitemobject
显示结果:
测量指标严重缺陷
037
102062
202897
select a.deviceitemobject as 测量指标 , count(a.id) as 一般缺陷
from checkresult a
where a.result=20
group by a.deviceitemobject
显示结果:
测量指标一般缺陷
012
103211
20965
我想显示成
测量指标一般缺陷 严重缺陷
012 37
103211 2062
20965 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
