[数据库] Oracle使用CASE判断解决多值问题丨【绽放吧！数据库】

select NAME, DECODE(SEX, 'Man', '男', 'Woman', '女', '无') as SEX
from PERSON;

select ZY_NAME, 
    DECODE(count(ZY_NAME), '1', '省示范专业', '2', '省示范、国家特色专业', '无') as TYPE
from ZY_TAB GROUP BY ZY_NAME;

select NAME, SEX, SAL, 
    CASE WHEN SAL<=2000 THEN '过低'
         WHEN SAL>=4000 THEN '过高'
         ELSE '正常'
     END AS STATE
from PERSON
order by NAME;

SELECT COUNT(*) AS 总人数,  
    COUNT(CASE WHEN HIGHEST_DEGREE='博士'THEN 1 END) AS 博士人数,   
    COUNT(CASE WHEN HIGHEST_DEGREE='硕士'THEN 1 END) AS 硕士人数,   
    COUNT(*)-COUNT(CASE WHEN HIGHEST_DEGREE='博士'THEN 1 END)-COUNT(CASE WHEN HIGHEST_DEGREE='硕士'THEN 1 END) AS 其他学历  
FROM TEACHER;  

select ZY_NAME, 
CASE WHEN COUNT(CASE WHEN ZY_TYPE='省示范专业' OR ZY_TYPE='国家特色专业' THEN 1 END)='2' 
     THEN '省示范专业; 国家特色专业' 
     WHEN COUNT(CASE WHEN ZY_TYPE='国家特色专业' THEN 1 END)='1' 
     THEN '国家特色专业'  
     WHEN COUNT(CASE WHEN ZY_TYPE='省示范专业' THEN 1 END)='1' 
     THEN '省示范专业'  
ELSE NULL END
AS TYPE 
from ZY_TAB group by ZY_NAME;

select ZY_NAME, ZY_CODE, 
CASE WHEN COUNT(CASE WHEN ZY_TYPE='省示范专业' OR ZY_TYPE='国家特色专业' THEN 1 END)='2' 
     THEN '省示范专业; 国家特色专业' 
     WHEN COUNT(CASE WHEN ZY_TYPE='国家特色专业' THEN 1 END)='1' 
     THEN '国家特色专业'  
     WHEN COUNT(CASE WHEN ZY_TYPE='省示范专业' THEN 1 END)='1' 
     THEN '省示范专业'  
ELSE NULL END
AS TYPE 
from ZY_TAB group by ZY_NAME, ZY_CODE;

select NAME, CODE, 
	(select
		CASE WHEN COUNT(CASE WHEN ZY_TYPE='省示范专业' OR ZY_TYPE='国家特色专业' THEN 1 END)='2' 
     	THEN '省示范专业; 国家特色专业' 
     	WHEN COUNT(CASE WHEN ZY_TYPE='国家特色专业' THEN 1 END)='1' 
     	THEN '国家特色专业'  
     	WHEN COUNT(CASE WHEN ZY_TYPE='省示范专业' THEN 1 END)='1' 
     	THEN '省示范专业'  
	ELSE NULL END 
		from ZY_TAB 
		where ZY_TAB.ZY_CODE=ZY.CODE
	) AS TYPE, INFO, PLACE
from ZY;