oracle case when 语句帮忙解决

2011-04-11 11:11:10 1.8k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ORACLE的CASE WHEN以不同列作为条件查询之前好几次做看板的时候，在使用ORACLE的case when时，一直找不到合适的例子，大多数的用法都是对查询的结果以case when 作为条件筛选，但是实际上很多条件或者串表的地方也是可以用到case when作为使用的手段的。以下就罗列以下使用的类型：一.简单CASE WHEN 的用法此方法是case when的一般用法，平常也是用的最多的，有2种语法，凭个人喜好使用： --简单Case函数 select CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' 阅读详情

Select A.StartTime as 时间分组,A.Counts 呼入总次数,nvl(B.Counts,0) 成功总次数,nvl(C.Counts,0) 失败总次数,

nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,

case 

when nvl(D.Counts,0)=0 then 0 else nvl(E.Counts,0)*100/D.Counts end as 转人工接通率(100%) --未找到from语句

else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数

endfrom

（select count(*) from *** ）A left outer join

（select count(*) from *** ）B left outer join

（select count(*) from *** ）C left outer join

（select count(*) from *** ）D left outer join

（select count(*) from *** ）E left outer join

在线等待。。。

...全文

树莓派国内镜像源一键配置指南本文提供树莓派国内镜像源一键配置指南，解决默认国外源访问缓慢问题。通过自动检测系统版本并支持清华、阿里云、中科大等主流镜像站选择，大幅提升软件更新与安装速度。包含详细的手动配置方法和常见问题排查，帮助用户快速完成树莓派换源优化。阅读详情

Jerss

Oracle 高级技术 3,499 成员 18,710 内容

+ 关注 + 加入社区

切换为时间正序

Jerss 2011-04-11

打赏
举报

[Quote=引用 20 楼 taikongxinke 的回复:]
不是很明白你的需求，“转人工接通率(100%)”，“转人工失败次数”这个是列名还是列的内容？
[/Quote]

列名

Jerss 2011-04-11

打赏
举报

[Quote=引用 19 楼 taikongxinke 的回复:]
case when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts else nvl(D.Counts,0)-nvl(E.Counts,0)) end 在这个基础上再做一个行列转换就可以了。
[/Quote]

列名

千骑卷平冈 2011-04-11

打赏
举报

弱弱的问一句，你最后一个“left outer join”连接哪个表。。。

taikongxinke 2011-04-11

打赏
举报

不是很明白你的需求，“转人工接通率(100%)”，“转人工失败次数”这个是列名还是列的内容？

taikongxinke 2011-04-11

打赏
举报

case when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts else nvl(D.Counts,0)-nvl(E.Counts,0)) end 在这个基础上再做一个行列转换就可以了。

Jerss 2011-04-11

打赏
举报

Select A.StartTime as 时间分组,A.Counts 呼入总次数,nvl(B.Counts,0) 成功总次数,nvl(C.Counts,0) 失败总次数,

nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,

case  

when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)

else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数

end  

from

上面所有列的数据都是从下面的select 语句中查出来的
我只是想在第五列做个判断
如果 nvl(D.Counts,0)=0 显示的结果为 nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
那么显示的结果为 (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数

Jerss 2011-04-11

打赏
举报

Select A.StartTime as 时间分组,A.Counts 呼入总次数,nvl(B.Counts,0) 成功总次数,nvl(C.Counts,0) 失败总次数,
nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,
case
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
end
from

(select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(*) as Counts
from SRRecord where StartTime>='2006-03-01 15:00:00','yyyy-mm-dd hh24:mi:ss') and StartTime<='2011-04-01 15:00:00','yyyy-mm-dd hh24:mi:ss')
and ServiceChainIndex = 0 --呼入总次数
) A left outer join

(select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(*) as Counts
from SRRecord where StartTime>=to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss') and StartTime<=to_date(p_StopTime,'yyyy-mm-dd hh24:mi:ss')
and (ServiceChainIndex = 1) AND (EndReason = '11') AND (ISO <> 'T')
) B on A.StartTime=B.StartTime left outer join --应答成功总次数

(select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(*) as Counts
from SRRecord where StartTime>='2006-03-01 15:00:00','yyyy-mm-dd hh24:mi:ss') and StartTime<='2011-04-01 15:00:00','yyyy-mm-dd hh24:mi:ss')
and ((ServiceChainIndex = 0) And (EndReason<>'7') And (EndReason<>'515')) OR ((ServiceChainIndex = 1) AND (EndReason <> '11') AND (ISO <> 'T') And (SPType is null))
) C on A.StartTime=C.StartTime left outer join --应答失败总次数

(select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(distinct SRID) as Counts
from SRRecord where StartTime>='2006-03-01 15:00:00','yyyy-mm-dd hh24:mi:ss') and StartTime<='2011-04-01 15:00:00','yyyy-mm-dd hh24:mi:ss')
and (EndReason = 20 and ISO<>'T' ) --IVR转座席次数，但不重复计算
) D on A.StartTime=D.StartTime left outer join

(select '2006-03-01 15:00:00~2011-04-01 15:00:00' as StartTime,count(distinct SRID) as Counts
from SRRecord where StartTime>='2006-03-01 15:00:00','yyyy-mm-dd hh24:mi:ss') and StartTime<='2011-04-01 15:00:00','yyyy-mm-dd hh24:mi:ss')
and (EndReason = 21 and ISO<>'T' ) --IVR转座席成功，但不重复计算
) E on A.StartTime=E.StartTime

Jerss 2011-04-11

打赏
举报

为什么不能定义两个别名？？？？

真的不知道取什么名 2011-04-11

打赏
举报

[Quote=引用 13 楼 jersslong 的回复:]
我只需要显示一列
当 nvl(D.Counts,0)=0 就显示为 nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
nvl(D.Counts,0)<>0 就显示为 (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
[/Quote]
仔细想想你的需求列名怎么可能会做到动态的呢？

真的不知道取什么名 2011-04-11

打赏
举报

[Quote=引用 12 楼 jersslong 的回复:]
只需要显示一列

ase
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数
[/Quote]
如果是一列的话你怎么能定义两个列别名呢？

Jerss 2011-04-11

打赏
举报

我只需要显示一列
当 nvl(D.Counts,0)=0 就显示为 nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
nvl(D.Counts,0)<>0 就显示为 (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数

Jerss 2011-04-11

打赏
举报

只需要显示一列

ase
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数

Jerss 2011-04-11

打赏
举报

不好意思多了个

else nvl(E.Counts,0)*100/D.Counts end as 转人工接通率(100%)

真的不知道取什么名 2011-04-11

打赏
举报

如果你是根据那个列的值来判断
想要显示两列的话请用两个case when...

Jerss 2011-04-11

打赏
举报

Select A.StartTime as 时间分组,A.Counts 呼入总次数,nvl(B.Counts,0) 成功总次数,nvl(C.Counts,0) 失败总次数,

nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,

case 

when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)

else nvl(E.Counts,0)*100/D.Counts end as 转人工接通率(100%)

else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数

end

from

（select count(*) as Counts from *** ）A left outer join

（select count(*) as Counts from *** ）B left outer join

（select count(*) as Counts from *** ）C left outer join

（select count(*) as Counts from *** ）D left outer join

（select count(*) as Counts from *** ）E left outer join

错误是：
when nvl(D.Counts,0)=0 then nvl(E.Counts,0)*100/D.Counts as 转人工接通率(100%)
as 缺失关键字

LiuHaoNan 2011-04-11

打赏
举报

起别名的话要有对象的，比如
when nvl(D.Counts,0)=0 then XXXX as 转人工接通率(100%)(100%) ；
你是不是打漏XXX了；
这个是语法错误，仔细看看啊，不带这么玩的

Jerss 2011-04-11

打赏
举报

“转人工接通率(100%)”
这是一列动态数据从
（select count(*) from *** ）D left outer join
（select count(*) from *** ）E left outer join
这两个查询语句中得

也就是（（select count(*) from *** ）D left outer join）-（（select count(*) from *** ）E left outer join） as 转人工接通率(100%)

LiuHaoNan 2011-04-11

打赏
举报

错误为在 case
when nvl(D.Counts,0)=0 then as--缺失关键字转人工接通率(100%)(100%)

then 要有内容的，你是对什么起的别名为 "转人工接通率(100%)(100%)"呢

Jerss 2011-04-11

打赏
举报

Select A.StartTime as 时间分组,A.Counts 呼入总次数,nvl(B.Counts,0) 成功总次数,nvl(C.Counts,0) 失败总次数,

nvl(D.Counts,0) 转人工总次数,nvl(E.Counts,0) 转人工成功次数,



case 

when nvl(D.Counts,0)=0 then as--缺失关键字 转人工接通率(100%)(100%) 

else (nvl(D.Counts,0)-nvl(E.Counts,0)) as 转人工失败次数

endfrom

（select count(*) from *** ）A left outer join

（select count(*) from *** ）B left outer join

（select count(*) from *** ）C left outer join

（select count(*) from *** ）D left outer join

（select count(*) from *** ）E left outer join

错误为在 case
when nvl(D.Counts,0)=0 then as--缺失关键字转人工接通率(100%)(100%)

taikongxinke 2011-04-11

打赏
举报

把问题说明白吧！都不知道你是哪个地方报错。

加载更多回复（7）

oracle case when 语句帮忙解决

相关推荐