邮箱为空则输出ID(ifnull比case when更方便)
select username,case when email is not null then email else user_id end from t_tfwx_user;
select username,ifnull(email,user_id) from t_tfwx_user;
isnull括号后值为null返回值1,否则为0
select isnull(1+1)->0
select isnull(1/0)->1
nullif(expre1,expre2),两参数相等则返回null,否则返回expre1
select nullif(1,1)->null
select nullif(1,2)->1
case when对成绩判断非常方便
- select username,case when yuwen>=80 then '优秀'
- when (yuwen<80 and yuwen>=60) then '及格'
- when (yuwen<60) then '差' end as '语文',
- case when shuxu>=80 then '优秀'
- when (shuxu<80 and shuxu>=60) then '及格'
- when (shuxu<60) then '差' end as '数学',
- case when yinyu>=80 then '优秀'
- when (yinyu<80 and yinyu>=60) then '及格'
- when (yinyu<60) then '差' end as '英语'
- from userinfo