今天遇到一个需求,需要用一个根据sql的两个字段的值相互关联,得到一个记录的3种状态。爬了同事的代码,看到这个语句格式,眼前一亮,sql如下:
SELECT m.*, CASE
WHEN m.work_id is null THEN '新增工单'
WHEN m.work_id is not null and w.anfnr is null THEN '同步工单'
WHEN m.work_id is not null and w.anfnr is not null THEN w.anfnr
ELSE '其他'
END erpWorkDoc
FROM JOB_ITEM m, ERPWORKDOC w
WHERE m.JOB_ID='ASDASDAS' AND w.jobItemId(+)=m.OBJ_ID
case中的多个when 只能有一个条件成立,类似于高级语言程序中switch case的default,ELSE可以不加
今天又用到这个了,很好很强大!
SELECT m.*, CASE
WHEN m.shutDown = '2' THEN ' '
ELSE '停役申请(' || to_char(select count(*) from wkm_outage j where j.job_id=t.obj_id) || ')'
END as outAge
FROM wkm_job t