条件判断语句 用于为查询字段的值添加判断条件，区别于when！- 常用于动态分区
- if
- select
- if(condition,true_value,false_value)
- from tb
- 例：对男女人数分别进行计算，
- 计算男的人数时，将性别男标记为数字1，女为数字0
- 计算女的人数时，将性别女标记为数字1，男为数字0
- 最终将数字累加
- select dept_id,
- sum(if(sex='男',1,0)) [as] malenum,
- sum(if(sex='女',1,0)) [as] femalenum
- from emp_sex group by dept_id;
- -------------------------------------------
- 统计每个部门男女各多少人
- 用group by：
- select dept_id,sex,count(*)
- from emp_sex group by dept_id,sex;
- =====================================================
- case when
- select dept_id,
- sum(case when sex='男' then 1 else 0 end) [as] malenum,
- sum(case when sex='女' then 1 else 0 end) [as] femalenum
- from emp_sex group by dept_id;
- ------------------------------------------------------
- select *,
- case
- when score >=80 then '优秀'
- when score >=60 and score <80 then '及格'
- when score >40 and score <60 then '不及格'
- else '渣'
- (when score <=40 then '渣')
- end [as] score_level
- from stu_score ；
- =====================================================
- nvl
- nvl(v1,v2) ,如果v1不为空，取v1，如果为空，取v2
- 注Ⅰ：select nvl(a.id,b.id) from a full join b on a.id=b.id ;
- <==> select nvl(b.id,a.id) from a full join b on a.id=b.id ;
- 注Ⅱ：select nvl(a.name,b.name) from a full join b on a.id=b.id ;
- <=/=> select nvl(b.name,a.name) from a full join b on a.id=b.id ;
- =====================================================
- coalesce
- coalesce(v1,v2,v3,v4...) 返回第一个非null的值
- 举例：
- coalesce(100,null,200,null,120) --> 100
- coalesce(null,200,null,120) --> 200
- 常用：coalesce(字段）
