oracle case when then
来源:互联网 发布:cr173破解软件 编辑:程序博客网 时间:2024/04/30 12:16
Oracle CASE WHEN 用法介绍
1. CASE WHEN 表达式有两种形式
--简单Case函数 CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END --Case搜索函数 CASEWHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '其他' END
2. CASE WHEN 在语句中不同位置的用法
2.1 SELECT CASE WHEN 用法
SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生,2位女生*/ ELSE NULL END) 男生数, COUNT (CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生数 FROM students GROUP BY grade;
2.3 WHERE CASE WHEN 用法
SELECT T2.*, T1.* FROM T1, T2 WHERE (CASE WHEN T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%' THEN 1 WHEN T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%' THEN 1 ELSE 0 END) = 1
2.4 GROUP BY CASE WHEN 用法
SELECT CASE WHEN salary <= 500 THEN '1' WHEN salary > 500 AND salary <= 600 THEN '2' WHEN salary > 600 AND salary <= 800 THEN '3' WHEN salary > 800 AND salary <= 1000 THEN '4' ELSE NULL END salary_class, -- 别名命名COUNT(*) FROM Table_A GROUP BY CASE WHEN salary <= 500 THEN '1' WHEN salary > 500 AND salary <= 600 THEN '2' WHEN salary > 600 AND salary <= 800 THEN '3' WHEN salary > 800 AND salary <= 1000 THEN '4' ELSE NULL END;
3.关于IF-THEN-ELSE的其他实现
3.1 DECODE() 函数
select decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')from employees;
貌似只有Oracle提供该函数,而且不支持ANSI SQL,语法上也没CASE WHEN清晰,个人不推荐使用。
3.2 在WHERE中特殊实现
SELECT T2.*, T1.* FROM T1, T2 WHERE (T2.COMPARE_TYPE = 'A' AND T1.SOME_TYPE LIKE 'NOTHING%') OR (T2.COMPARE_TYPE != 'A' AND T1.SOME_TYPE NOT LIKE 'NOTHING%')
这种方法也是在特殊情况下使用,要多注意逻辑,不要弄错。
--@
insert into sd_claiml(
ClaimNo,
GroupClaimNo,
ClaimType,
ClaimDate,
LossDate,
LossCause,
LossArea,
Name,
Gender,
Birthday,
CredentialType,
CredentialNo,
LossResult,
LossResultDate,
IsCancelClaim,
CancelDate,
makedate
)
select rg.rgtno,
'',
'02',
to_char(rg.rgtdate, 'yyyymmddhh'),
to_char(rg.accidentdate, 'yyyymmddhh'),
'01',
'',
lp.name,
lp.sex,
to_char(lp.birthday, 'yyyymmdd'),
(case trim(lp.idtype)
when '0' then
'01'
when '1' then
'07'
when '2' then
'04'
when '3' then
'03'
when '5' then
'02'
when '6' then
'09'
else
'99'
end),
lp.idno,
(case (select SUBSTR(lar.reasoncode, 2, 4)
from llappclaimreason lar
where lar.caseno = rg.rgtno
and rownum = 1)
when '01' then
'03'
when '03' then
'02'
when '02' then
'01'
else
'99'
end),
to_char(rg.accidentdate, 'yyyymmddhh'),
'0',
'',
sysdate
from llregister rg, ldperson lp, llcase lc
where rg.rgtno = lc.caseno
and lc.customerno = lp.customerno
and rg.mngcom like '8606%'
-- and rg.makedate = trunc(sysdate-1)
and rg.makedate = trunc(sysdate)
and substr(rg.maketime,0,2)=to_char(sysdate-1/24,'hh24')
- oracle case when then
- oracle case when then
- oracle case when then
- Oracle case when then else
- oracle case when then 条件判断
- oracle left join case when then
- Oracle中的case when then 用法.
- [ORACLE] case when then else end 应用
- [ORACLE] case when then else end 应用
- oracle case when then end 语句
- oracle case when then else end
- [ORACLE] case when then else end 应用
- [ORACLE] case when then else end 应用
- [ORACLE] case when then else end 应用
- oracle 存储过程 case when then
- case when then else
- mssql ,case ,when,then
- case when then else
- Linux common clock framework(3)_概述
- fullcalendar 日历改造
- composer的安装
- java虚拟机
- Java基础------------------ 集合框架 Map、proproperties
- oracle case when then
- 关于跨dll的模板单例问题
- MySQL中的WHERE子句
- NSFileManager 文件管理详细介绍
- 一些报错总结
- 隐马尔可夫模型(HMM)实现分词
- a标签中href=""的用法详解
- 使用bower安装bootstrap遇到的问题
- bzoj 3064: Tyvj 1518 CPU监控 (线段树)