原创

MySQL-group by-子查询sum求和

SELECT
  ct.NAME AS courseName,
  IFNULL( ulc.progress, 0.00 ) AS learningProgress,
  ifnull(
      ( SELECT sum( duration ) FROM inp_course_task_user_learning_record WHERE course_id = ct.course_id AND member_id = ctm.member_id AND is_delete = 0 ),
      0
    ) AS learningTime,
  ctulr.update_time AS lastLearningTime
FROM
  inp_course_task ct
    LEFT JOIN inp_course_task_member ctm ON ctm.course_id = ct.course_id
    LEFT JOIN inp_contr_member cm ON cm.id = ctm.member_id
    LEFT JOIN inp_user_learning_course ulc ON ulc.course_id = ctm.course_id
    AND ulc.ur_id = cm.id
    AND ulc.or_id = cm.or_id
    LEFT JOIN inp_course_task_user_learning_record ctulr ON ctulr.course_id = ct.course_id
    AND ctulr.member_id = ctm.member_id
    AND ctulr.is_delete = 0
WHERE
    ctm.container_id = 'BaoXianFuWu'
  AND cm.NAME = '测试人员042802'
  AND cm.id_card_number = '32011119961106xxx'
  AND ct.is_delete = 0
GROUP BY
  ct.course_id;

注意explain看一下性能问题，两个表可能相互牵连

正文到此结束

所属分类：数据库

本文标签： MySQL
本文链接： https://code.jiangjiesheng.cn/article/296
版权声明： 本文由小江同学原创发布，转载请先联系本站长，谢谢。

MySQL-group by-子查询sum求和

热门推荐

相关文章

说给你听

本文目录

标签云

近期评论

网站信息