需求
现在有一份管线数据,表中含有字段buildTime说明了管线的建设日期,我们需要按时间段统计管线的数据
这是,我们需要对管线表中建设日期所对应的字段进行分类
实现如下:
实现
对现有的数据进行分类判断过滤,使用到了 case when
case when
case when 实现了对数据的不同范围进行判断过滤并重新规范的过程,类似于 while(个人理解)
语法:
CASE
WHEN condition1 THEN result1
WHEN condistion2 THEN result2
...
WHEN condistionN THEN resultN
ELSE default_result
END as judgeName //命名别名
上图实现
select nvl(cd, 0) as cd,
case
when jsnd between
to_date(2007-12-31 000000', 'yyyy-mm-dd HH24miss') and
to_date('2017-12-31 000000', 'yyyy-mm-dd HH24miss') then
'10'
when jsnd between
to_date('1987-12-31 000000', 'yyyy-mm-dd HH24miss') and
to_date('2007-12-31 000000', 'yyyy-mm-dd HH24miss') then
'1030'
when jsnd between
to_date('1967-12-31 000000', 'yyyy-mm-dd HH24miss') and
to_date('1987-12-31 000000', 'yyyy-mm-dd HH24miss') then
'3050'
when jsnd
to_date('1967-12-31 000000', 'yyyy-mm-dd HH24miss') then
'50'
else
'unkown'
end as timeClassification
from CLZHGWSDE.JS_GX_PL) GX