mysql根据某个字段去重

mysql字段去重，根据一个字段去重，聚合查询去重

需求

查询出相同班级id的打卡记录数

方案

在count 里使用 distinct  对字段去重

数据原型

统计出来user_id是两个才对

去重前，num6预约详情

SELECT
 sc.*,
     count(lr.user_id ) num 
 FROM
     `student_class` `sc`
     INNER JOIN `student` `s` ON `sc`.`id` = `s`.`class_id`
     INNER JOIN `user_student` `us` ON `us`.`student_id` = `s`.`id`
     INNER JOIN `lecture_record` `lr` ON `lr`.`user_id` = `us`.`user_id` 
 WHERE
     `lr`.`lecture_id` = 1 
 GROUP BY
     `sc`.`id`

去重后 num 为2 正确

SELECT
 sc.*,
     count( DISTINCT lr.user_id ) num 
 FROM
     `student_class` `sc`
     INNER JOIN `student` `s` ON `sc`.`id` = `s`.`class_id`
     INNER JOIN `user_student` `us` ON `us`.`student_id` = `s`.`id`
     INNER JOIN `lecture_record` `lr` ON `lr`.`user_id` = `us`.`user_id` 
 WHERE
     `lr`.`lecture_id` = 1 
 GROUP BY
     `sc`.`id`