A Database Error Occurred Error Number: 1066 Not unique tabl

Question

嘿，我正在构建非常复杂的查询，我不知道它是否计算复杂但由于我是SQL新手，这是相当复杂的

错误信息:

A Database Error Occurred错误编号:1066不是唯一的表/别名:'bb'

 SELECT cl.*,
            (CASE  
                WHEN id_student IS NOT NULL THEN 'BOOKED' ELSE 
                (
                    CASE 
                    WHEN (max_count - student_count) > 0 THEN 'AVAILABLE' ELSE 'FULL'
                    END
                )
            END) AS course_status        
        FROM (    
                SELECT         aa.*, bb.id_student, cc.max_count, dd.student_count
                FROM        (
                                    SELECT c.id_course, c.id_study, c.id_level, c.id_teacher, c.course_date, 
                                s.study_name,
                                t.teacher_name,
                                t.teacher_picture
                                FROM tbl_course c
                                LEFT JOIN tbl_study s ON s.id_study = c.id_study
                                LEFT JOIN tbl_teacher t ON t.id_teacher = c.id_teacher
                                WHERE
                                CONCAT('"', REPLACE(c.id_level, ',', '","'), '"') LIKE '%"64"%'
                                AND c.id_study = '2'
                                AND c.course_date 
                                AND c.id_study = '2'AND c.course_date 
                                     BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" ORDER BY c.course_date ASC
                                    ) aa
                LEFT JOIN      (
                                        SELECT     id_student, id_course
                                        FROM     tbl_course_student 
                                            WHERE id_student = '305'
                                              AND id_study = '2'
                                    ) bb ON aa.id_course = bb.id_course
                LEFT JOIN        (
                                    SELECT SUM(student_count_max) AS max_count, course_date
                                    FROM tbl_course
                                    WHERE id_study = '2'
                                         AND course_date BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" 
                                              AND id_study = '2') bb ON bb.id_course = aa.id_course 
                LEFT JOIN        (
                                    SELECT SUM(student_count_max) AS max_count, course_date
                                    FROM tbl_course
                                    WHERE id_study = '2'AND course_date BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" 
                                         AND CONCAT('"', REPLACE(id_level, ',', '","'), '"') LIKE '%"64"%'
                                    GROUP BY course_date, id_study
                                ) cc ON aa.course_date = cc.course_date
                LEFT JOIN       (
                                        SELECT SUM(student_count) AS student_count, course_date
                                  FROM      tbl_course 
                                    WHERE id_study = '2'
                                         AND course_date BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" 
                                         AND CONCAT('"', REPLACE(id_level, ',', '","'), '"') LIKE '%"64"%'
                                         AND course_date BETWEEN "2016-10-02 00:00:00" AND "2016-10-09 23:59:59" AND CONCAT('"', REPLACE(id_level, ',', '","'), '"') LIKE '%"64"%'
                                    GROUP BY course_date, id_study
                                     ) dd ON aa.course_date = dd.course_date
        ) cl
        GROUP BY course_date, course_status

我的问题:我哪里做错了?我的意思是，我一遍又一遍地读这些问题，但我什么也没找到。

Answer 1

你有2倍的别名'bb'

第一次join:

) bb ON aa.id_course = bb.id_course

和这里的第二个join:

AND id_study = '2') bb ON bb.id_course = aa.id_course 

您需要将其中一个重命名为另一个别名(并在您的用法中正确映射它)。

Answer 2

您正在为两个内部查询使用bb别名。你不能那样做。每个别名必须是唯一的