这样的东西能用一个SQL语句搞出来么

[copico]

RSC_CD HC_STATUS2 IS_FULLFILL TAT R_REPAIR

Y010 B N 178 Y
A010 D Y 112 N
C010 B N 157 Y

怎么能用最少的最高效果SQL语句，最好效果要高，因为数据量比较大算出

当 HC_STATUS2 in ('B','C') / HC_STATUS2 in ('B','C','D','V')

IS_FULLFILL = 'Y' / ALL

TAT的平均数量

R_REPAIR = 'Y' /all

当然不用查询出来，保存为变量也可以，最重要的代码要精，要效率

日前我的笨办法是要一个结果，就查询一次，然后再做相除，总供做了8次查询，我觉得很不效率，有其它办法么

[dawugui]

当 HC_STATUS2 in ('B','C') / HC_STATUS2 in ('B','C','D','V')

IS_FULLFILL = 'Y' / ALL

TAT的平均数量

R_REPAIR = 'Y' /all

上面的这些看不懂.

[冷箫轻笛]

...

没看懂

[Yang_]

select
1.0*sum(case when HC_STATUS2 in ('B','C') then 1 else 0 end)/sum(case when HC_STATUS2 in ('B','C','D','V') then 1 else 0 end) as data1,
1.0*sum(case when IS_FULLFILL = 'Y' then 1 else 0 end)/sum(1) as data2,
avg(TAT) as data3,
1.0*sum(case when R_REPAIR = 'Y' then 1 else 0 end)/sum(1) as data4
from tablename

[marco08]

沒看懂, 學習

[copico]

IS_FULLFILL = 'Y' / ALL
的意思是
求出 IS_FULLFILL = 'Y' 的值，和总记录数，然后求除法

[dawugui]

你干脆给个结果吧.
不然头大.

[冷箫轻笛]

Yang_(扬帆破浪) 正解！！

[copico]

Yang_(扬帆破浪)
的OK，可是如果sum(case when HC_STATUS2 in ('B','C','D','V') = 0 了怎么办

[Yang_]

select
case when sum(case when HC_STATUS2 in ('B','C','D','V') then 1 else 0 end)=0 then 0 else 1.0*sum(case when HC_STATUS2 in ('B','C') then 1 else 0 end)/sum(case when HC_STATUS2 in ('B','C','D','V') then 1 else 0 end) end as data1,
1.0*sum(case when IS_FULLFILL = 'Y' then 1 else 0 end)/sum(1) as data2,
avg(TAT) as data3,
1.0*sum(case when R_REPAIR = 'Y' then 1 else 0 end)/sum(1) as data4
from tablename

[copico]

ok,结帖