case when在sqlserver和oracle的不同寫法

---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
;      

轉載于:https://blog.51cto.com/ocpyang/1300170