mysql - 错误 #1066 - MySQL 中的表/别名不唯一

Question

我的 SQL 语句有问题，出现错误

#1066 - Not unique table/alias: 'students'

下面是我的代码:

SELECT batches.*,students.*, courses.course_name AS course_name,
count(DISTINCT IF(attendances.student_id = students.id AND attendances.permitted = 0,attendances.id ,NULL)) AS absents_count,
count(DISTINCT IF(batches.id = students.batch_id ,students.id ,NULL)) AS students_count, count(DISTINCT IF(attendances.student_id = students.id AND attendances.permitted = 1,attendances.id ,NULL)) AS leaves_count 
FROM `batches`   
INNER JOIN courses ON courses.id = batches.course_id 
LEFT OUTER JOIN attendances ON attendances.batch_id = batches.id AND attendances.month_date = '2016-09-05'  
LEFT OUTER JOIN students ON students.id = attendances.student_id AND students.batch_id = batches.id 
INNER JOIN students ON  batches.id = students.batch_id 
WHERE ('2016-09-05' BETWEEN batches.start_date AND batches.end_date AND batches.is_active = 1 AND batches.is_deleted = 0 AND courses.is_deleted = 0 ) 
GROUP BY batches.id 
ORDER BY courses.course_name,batches.id 
LIMIT 0, 10;

leaves_count 和absents_count 工作正常，但是当我为students_count 添加代码时，它会给出上述错误。

Answer 1

对表students和所有相关列使用不同的别名

select <Fields to be seleced>
FROM `batches`   
INNER JOIN courses ON courses.id = batches.course_id 
LEFT OUTER JOIN attendances ON attendances.batch_id = batches.id AND attendances.month_date = '2016-09-05'  
LEFT OUTER JOIN students st1 ON st1.id = attendances.student_id AND students.batch_id = batches.id 
INNER JOIN students st2 ON  batches.id = st2.batch_id 
WHERE ('2016-09-05' BETWEEN batches.start_date AND batches.end_date AND batches.is_active = 1 AND batches.is_deleted = 0 AND courses.is_deleted = 0 )  GROUP BY batches.id ORDER BY courses.course_name,batches.id LIMIT 0, 10;