mysql 有感

#话题
SELECT cm.id,
  cm.cm_alias_name,
  cm.cm_cover_url
  ,ifnull(aa.view_num/bb.total,0) view_progress
  ,aa.view_num
  ,bb.total
FROM custom_module cm
LEFT JOIN (
            SELECT bb.cm_id ,uvr.user_id, count(uvr.video_id) view_num,uvr.last_view_time
           FROM user_video_rel uvr
             inner JOIN (
                          SELECT video_id,aa.cm_id
                          FROM coursepackage_video_rel cvr
                            INNER JOIN (
                                         SELECT cvtr.coursepackage_id id ,cm.id cm_id
                                         FROM custom_module cm
                                           inner JOIN coursepackage_video_type_rel cvtr ON FIND_IN_SET(cvtr.video_type_id,REPLACE(cm.cm_program_id_two,';',
                                                                                                                                  ','))
                                         where cm.cm_type = 5
                                         GROUP BY cm.id, cvtr.coursepackage_id
                                       ) aa ON aa.id = cvr.course_package_id
                          GROUP BY aa.cm_id,video_id
                        ) bb ON bb.video_id = uvr.video_id
           WHERE uvr.user_id = #{userId} AND (uvr.is_finish or uvr.is_finish_audio)
           group by bb.cm_id
           ORDER BY uvr.last_view_time DESC)aa ON aa.cm_id = cm.id
  LEFT JOIN (SELECT
               count(bb.video_count) total,
               bb.cm_id
             FROM (
                    SELECT
                      count(cvr.video_id) video_count,
                      cm.id               cm_id
                    FROM coursepackage_video_type_rel cvtr
                      INNER JOIN custom_module cm
                        ON cm.cm_type = 5 AND FIND_IN_SET(cvtr.video_type_id,
                                                          REPLACE(cm.cm_program_id_two,
                                                                  ';', ','))
                      INNER JOIN coursepackage_video_rel cvr ON cvtr.coursepackage_id = cvr.course_package_id
                    GROUP BY cm.id, cvr.video_id
                  ) bb
             GROUP BY bb.cm_id)bb ON bb.cm_id = cm.id
WHERE cm.cm_type = 5
;



### 同学
select aa.user_id,aa.view_num ,bb.total_num,aa.view_num/bb.total_num view_progress,
                                            ui.id user_id,ui.user_name,ui.pic_name,ui.head_img_url,if(gi.id is NULL ,0,1) is_glory,aa.last_view_time
                                                                     access_time
from
  custom_module cm
  inner join (
            SELECT aaa.cm_id ,aaa.user_id, aaa.last_view_time,count(aaa.video_id) view_num
            FROM (SELECT bb.cm_id ,uvr.user_id, uvr.last_view_time
                    ,uvr.video_id,(uvr.is_finish or uvr.is_finish_audio)
                  FROM user_video_rel uvr
                    inner JOIN (
                                 SELECT video_id,aa.cm_id
                                 FROM coursepackage_video_rel cvr
                                   INNER JOIN (
                                                SELECT cvtr.coursepackage_id id ,cm.id cm_id
                                                FROM custom_module cm
                                                  inner JOIN coursepackage_video_type_rel cvtr ON FIND_IN_SET(cvtr.video_type_id,REPLACE(cm.cm_program_id_two,';',
                                                                                                                                         ','))
                                                where cm.cm_type = 5 and cm.id =#{customModuleId}
                                                GROUP BY cvtr.coursepackage_id
                                              ) aa ON aa.id = cvr.course_package_id
                                 GROUP BY video_id
                               ) bb ON bb.video_id = uvr.video_id
                  WHERE  (uvr.is_finish or uvr.is_finish_audio) #uvr.user_id = #{userId} AND
                  )   aaa
                group by aaa.user_id
            ORDER BY aaa.last_view_time DESC
             ) aa on aa.cm_id = cm.id
  inner join (
               select bb.cm_id ,count(bb.video_id) total_num
               from(
                     SELECT video_id,aa.cm_id
                     FROM coursepackage_video_rel cvr
                       INNER JOIN (
                                    SELECT cvtr.coursepackage_id id ,cm.id cm_id
                                    FROM custom_module cm
                                      inner JOIN coursepackage_video_type_rel cvtr ON FIND_IN_SET(cvtr.video_type_id,REPLACE(cm.cm_program_id_two,';',
                                                                                                                             ','))
                                    where cm.cm_type = 5 and cm.id =#{customModuleId}
                                    GROUP BY cvtr.coursepackage_id
                                  ) aa ON aa.id = cvr.course_package_id
                     GROUP BY video_id
                   ) bb
             ) bb on bb.cm_id = cm.id
  INNER JOIN user_info ui ON aa.user_id = ui.id AND ui.disable_flag = 0
  LEFT JOIN teacher_info ti ON ui.id = ti.user_id
  LEFT JOIN glory_info gi ON gi.user_id = ui.id
WHERE 1=1
GROUP BY aa.user_id

ORDER BY view_progress DESC,access_time DESC

mysql 有感（前面一条话题sql由于user_id 把一对多，变成了一对一，所以count产生了作用 ）

后一条：则必须通过套多一层select，来计算数据。