case when在sqlserver和oracle的不同写法

原创

ocpyang 2013-09-22 16:43:21 博主文章分类：oracle ©著作权

文章标签 case when在sqlserver和 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者ocpyang的原创作品，请联系作者获取转载授权，否则将追究法律责任

---sqlserver写法：
SELECT COUNT(1),remark FROM (            
 SELECT   Remark=CASE 
 WHEN Remark LIKE '%CHECK TKT TIME%'  THEN '保留时间有问题'
 WHEN Remark LIKE '%WITH "AV" AGAIN%' THEN '没有舱位'
 WHEN Remark LIKE 'DUPLICATE TEL NUMBER%' THEN '订位授权OFFICE号重复'
  WHEN Remark LIKE '%INVALID FOID%' THEN '证件号错误'
 ELSE '其他' end FROM JinRiLogger.dbo.UserOperateLog201307 WITH(NOLOCK)
 WHERE OperateType=8
 ) AS  a GROUP BY a.Remark

---oracle写法:
SELECT
CASE  
 WHEN Remark LIKE '%CHECK TKT TIME%'  THEN '保留时间有问题'
 WHEN Remark LIKE '%WITH "AV" AGAIN%' THEN '没有舱位'
 WHEN Remark LIKE 'DUPLICATE TEL NUMBER%' THEN '订位授权OFFICE号重复'
 WHEN Remark LIKE '%INVALID FOID%' THEN '证件号错误'
 ELSE '其他' 
end
,
count(*)
FROM JinRiLog.UserOperateLog partition (p2)
where Operatetype=8
group by
CASE  
 WHEN Remark LIKE '%CHECK TKT TIME%'  THEN '保留时间有问题'
 WHEN Remark LIKE '%WITH "AV" AGAIN%' THEN '没有舱位'
 WHEN Remark LIKE 'DUPLICATE TEL NUMBER%' THEN '订位授权OFFICE号重复'
 WHEN Remark LIKE '%INVALID FOID%' THEN '证件号错误'
 ELSE '其他'
end
;