1.Decode函数
直接介绍如何用.
SELECT id,name
DECODE(flag,'1','True','0','false',NULL,'None','Invalid')
字段 | 判断字段值,显示具体含义 | 默认值
FROM UserInfo;
直接介绍如何用.
SELECT id,name
DECODE(flag,'1','True','0','false',NULL,'None','Invalid')
字段 | 判断字段值,显示具体含义 | 默认值
FROM UserInfo;
decode(m.totalcount,null,0,m.totalcount)
在进行报表统计的时候,有的字段无记录,这时候需要把统计置为0,如果有记录返回本身值.
2.Case语句用法.
select rownum as rn ,TERMINATEID,CMPTSCODE ,ERRLEVEL, ERRCODE ,ERRSTATE,
(CASE T.ERRSTATE WHEN '0' THEN '未发生故障' WHEN '1' THEN '有故障未申报' WHEN '2' THEN '有故障已申报' WHEN '3' THEN '已处理' END)
ERRSTATENAME,ERRTIME ,REMARK from WX_CMPTSSTATE T
和Decode用法差不多,但是这个比较和IF-ELSE接近,符合大家的习惯写法.
3.树查询
--查询位置 011 以下的所有孩子
select * from ( select wk_pst.*,level from wk_pst
connect by prior pos_code =prt_pos_code
start with pos_code='011'
) a
where a.pos_code<>'011'
--伪列Level的应用
select * from ( select wk_pst.*,level as ll from wk_pst
connect by prior pos_code =prt_pos_code
start with pos_code='011'
) a
where a.pos_code<>'011' and a.ll =3
select * from ( select wk_pst.*,level as ll from wk_pst
connect by prior pos_code =prt_pos_code
start with pos_code='011'
) a
where a.pos_code<>'011' and a.ll =3
--倒着查，注意递归条件的写法，仅仅置换了一下而已，注意伪列的变化
select wk_pst.*,level as ll from wk_pst
connect by prior prt_pos_code=pos_code
start with pos_code='111'
order by pos_code
select wk_pst.*,level as ll from wk_pst
connect by prior prt_pos_code=pos_code
start with pos_code='111'
order by pos_code
4 SQL中的CONVERT Oracle中的to_Char和cast
SQL中的用法:
SELECT CONVERT(varchar(30),getdate(),101) now
Oracle:
SELECT to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')
SELECT Cast(字段 as 类型)