高级sql 使用

-- IFNULL 如果获取里面数据为空 用 后面第二个参数0 替代
-- IFNULL(substr(study_finish, 3, 1), 0)   
-- 例子study_finish = 1,0,0,0    红字意思: 取字符顺数第3个字符0 

select
IFNULL(substr(study_finish, 7, 1), 0) as 第四节课完课,
IFNULL(substr(study_finish, 5, 1), 0) as 第三节课完课,
IFNULL(substr(study_finish, 3, 1), 0) as 第二节课完课,
IFNULL(substr(study_finish, 1, 1), 0) as 第一节课完课,
IFNULL(substr(study_complete_work , 7, 1), 0) as 第四节课作业,
IFNULL(substr(study_complete_work , 5, 1), 0) as 第三节课作业,
IFNULL(substr(study_complete_work , 3, 1), 0) as 第二节课作业,
IFNULL(substr(study_complete_work , 1, 1), 0) as 第一节课作业
from tbl_b2c_user_study_source_v2
where study_attend IS NOT NULL

if语句的使用
-- 例子study_finish = 1,0,0,0
select
IF(study_finish like '1,1,1,1%' and study_complete_work like '1,1,1,1%', 1, 0) as 4节课和作业均完成判断,
user_id , study_complete_work as 作业完成情况, study_finish as 课程完成情况 FROM tbl_b2c_user_study_source_v2 where study_attend IS NOT NULL

内嵌判断

IF(c.landing_type=3,'小火箭',IF(c.landing_type=1,'小火箭1','探月2')) as 课包类型