查询错误（1066）-MySQL

艾伦·凯尔·鲍尔

我在MySQL 5.5.3下执行此查询时遇到了一些问题。它在查询（1066）中返回错误：非唯一表/别名：“类别”

SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers
FROM mdl_course AS courses, mdl_course_categories AS categories
LEFT JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments  AS lra ON lra.contextid = ctx.id
JOIN mdl_role_assignments  AS tra ON tra.contextid = ctx.id
JOIN mdl_course_categories AS categories ON courses.category = categories.id
WHERE courses.category = categories.id
AND (
    categories.path LIKE '%/9/%' 
    OR categories.path LIKE '%/9'
    )
AND lra.roleid=5
AND tra.roleid=3

我有点迷茫，因为我已经为列以及表名加上了别名。谁能发现我可能在哪里出问题了？

阿比克·查克拉博蒂（Abhik Chakraborty）

您有一条选择语句为

FROM mdl_course AS courses, mdl_course_categories AS categories

然后

JOIN mdl_course_categories AS categories ON courses.category = categories.id

这使其成为非唯一别名

您应该删除选择部分，并且应为

SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers
FROM mdl_course AS courses
LEFT JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments  AS lra ON lra.contextid = ctx.id
JOIN mdl_role_assignments  AS tra ON tra.contextid = ctx.id
JOIN mdl_course_categories AS categories ON courses.category = categories.id

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。