decode('1',
'1','第1季度',
'2','第2季度',
'3','第3季度',
'4','第4季度',
'') 相当于判断
select instr('helloworld','wo') from dual; --返回结果:6 即“w”开始出现的位置 定位返回值
ORACLE WITH AS 用法
with TT as(
select 2019 as year ,decode('1',
'1',
'第1季度',
'2',
'第2季度',
'3',
'第3季度',
'4',
'第4季度',
'') as jd
from dual
)
select * from TT
with para as
(select 2019 year, '1' jd from dual)
select (case t1.jd
when '1' then
t1.year || '03'
when '2' then
t1.year || '06'
when '3' then
t1.year || '09'
else
t1.year || '12'
end) end_month
from para t1
NVL(Expr1,Expr2)如果Expr1为NULL,返回Expr2的值,否则返回Expr1的值
instr(:orgcode, to_char(t.aaa027)) > 0
with para as
(select 2019 year, '1' jd from dual),
get_month as (
select (case t1.jd
when '1' then
t1.year || '03'
when '2' then
t1.year || '06'
when '3' then
t1.year || '09'
else
t1.year || '12'
end) end_month
from para t1
)
(select * from para)
with as语法
相当于建了个临时表 ,或者sql的别命名
-
with
-
tmp1 as(select * from aa where id="001"),
-
tmp2 as(select * from bb where id="001"),
-
tmp3 as(select* from cc where id="001"),
-
...select *from tmp1 , tmp2,tmp3 ... where tmp1.id=tmp2.id=tmp3.id='001' ;
-
--相当于
-
create table tmp1 as select * from aa
-
-
create table tmp2 as select * from bb
-
-
create table tmp3 as select * from cc
-
-
select * from tmp1 ,tmp2,tmp3 where tmp1.id=tmp2.id=tmp3.id='001' ;