oracle查询排名榜(除数不能为0)
select 人,成功数,失败数,未启动,已启动,成功加失败总数,成功率,rank() over (order by 成功率 desc) 名次 from(
select user_id as 人,
count(*) as 总计划条数,
sum(case when status='2' then 1 else 0 end)成功数,
sum(case when status='3' then 1 else 0 end) 失败数,
sum(case when status='0' then 1 else 0 end) 未启动,
sum(case when status='1' then 1 else 0 end) 已启动,
(sum(case when status='2' then 1 else 0 end)+sum(case when status='3' then 1 else 0 end)) 成功加失败总数,
ROUND(100*sum(case when status='2' then 1 else 0 end) /
(sum(case when status='2' then 1 else 0 end)+sum(case when status='3' then 1 else 0 end) )) 成功率
from lb_plan group by user_id order by 成功率 desc
) where rownum <=100
但是在这里,没有排除为除数
select 人,成功数,失败数,未启动,已启动,成功加失败总数,成功率,rank() over (order by 成功率 desc) 名次 from(
select user_id as 人,
count(*) as 总计划条数,
sum(case when status='2' then 1 else 0 end)成功数,
sum(case when status='3' then 1 else 0 end) 失败数,
sum(case when status='0' then 1 else 0 end) 未启动,
sum(case when status='1' then 1 else 0 end) 已启动,
(sum(case when status='2' then 1 else 0 end)+sum(case when status='3' then 1 else 0 end)) 成功加失败总数,
ROUND(100*sum(case when status='2' then 1 else 0 end) /decode(sum(case when status='2' then 1 else 0 end)+sum(case when status='3' then 1 else 0 end),0 ,1,
sum(case when status='2' then 1 else 0 end)+sum(case when status='3' then 1 else 0 end) )) 成功率
from lb_plan group by user_id order by 成功率 desc
) where rownum <=100
decode (expression, search_1, result_1, default)
杀死进程
taskkill /f /t /im opera.exe
如果 expression结果=search_1结果,则返回result_1,否则反回default