oracle中使用count加条件统计

错误写法：

select count(sc.score>6) from stu_score sc

1：使用最普遍的 where 条件
COUNT(1) FROM 表 WHERE IS_VALID = 0

2：使用 cse when

• case when XX then XX else XX end;
• case XX when XX then XX when XX then xx else XX end;

 COUNT(
 	CASE 
 		IS_VALID 
 	WHEN 0 
 	THEN 
 			CASE 
 				INVALID_CAUSE 
 			WHEN 1 
 			THEN 1 
 			ELSE NULL 
 			END 
 	ELSE NULL 
 	END) 

3: 使用 decode
计算出1980,1981,1982三年入职的员工总人数和每年入职人数.

select count(empno) total,
count(decode(to_char(HIREDATE,'yyyy'),'1980',1,null)) "1980",
count(decode(to_char(HIREDATE,'yyyy'),'1981',1,null)) "1981",
count(decode(to_char(HIREDATE,'yyyy'),'1982',1,null)) "1982"
from emp 
where to_char(HIREDATE,'yyyy') in ('1980','1981','1982'

4: 使用 sum 与 case when

select sum(case when t.command_name = 'UNLOCK' then 1 else 0 end)) from t