数据库里类switch语句和三元运算符的使用

-- oracle 

SELECT 

SUBSTR(msterialLogId,1,2) || '-' || SUBSTR(msterialLogId,3,2) || '-' || SUBSTR(msterialLogId,5,2) || '-' || SUBSTR(msterialLogId,7,3) AS "编号" ,

DECODE(mClass,'A',TO_CHAR(mDosage,'99999'),' ') AS "甲材料的数量" ,

DECODE(mClass,'B',TO_CHAR(mDosage,'99999'),' ') AS "乙材料的数量" ,

DECODE(mClass,'C',TO_CHAR(mDosage,'99999'),' ') AS "丙材料的数量" ,

DECODE(mClass,'D',TO_CHAR(mDosage,'99999'),' ') AS "丁材料的数量" ,

DECODE(mClass,'E',TO_CHAR(mDosage,'99999'),' ') AS "戊材料的数量"

FROM mdosage;

-- mysql

SELECT 

CONCAT(SUBSTR(msterialLogId,1,2) , '-', SUBSTR(msterialLogId,3,2) , '-' , SUBSTR(msterialLogId,5,2) , '-' , SUBSTR(msterialLogId,7,3)) AS "编号",

CASE WHEN mClass = '甲' THEN mDosage ELSE " " END AS "甲材料的数量" ,

CASE WHEN mClass = '乙' THEN mDosage ELSE " " END AS "乙材料的数量" ,

CASE WHEN mClass = '丙' THEN mDosage ELSE " " END AS "丙材料的数量" ,

CASE WHEN mClass = '丁' THEN mDosage ELSE " " END AS "丁材料的数量" ,

CASE WHEN mClass = '戊' THEN mDosage ELSE " " END AS "戊材料的数量"

FROM mdosage; 

结果如下图：