SQL子查询

Question

我需要在一个(用户)的(课程)中获得(已完成模块的计数)和(课程模块的计数)。

表：

course_modules - will have all the course modules
course_modules_completion - will have all the users with completed modules.

我希望在一个查询中显示所有这三个查询，如示例：

(course)  (count of course modules) (count of completed modules) (userid)
   3               20                        5                      12
   5               20                        8                      7

以下是我尝试的查询：

 SELECT
    c.fullname, count(cm.id) AS "coursemodules", cmc.userid,
    (SELECT count(cmc.coursemoduleid) 
     FROM mdl_course c 
         JOIN mdl_course_modules cm 
         JOIN mdl_modules m 
         JOIN mdl_course_modules_completion cmc ON c.id = cm.course 
           AND cm.module = m.id 
           AND cmc.coursemoduleid = cm.id 
           AND cmc.userid = 12 
           AND cm.course = 7 
           AND cmc.completionstate BETWEEN 1 AND 10) AS "completedmodules"
    FROM
    mdl_course c 
       JOIN mdl_course_modules cm 
       JOIN mdl_modules m 
          ON c.id = cm.course AND cm.module = m.id AND cm.course = 7

它显示一个错误：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT count(cmc.coursemoduleid) FROM mdl_course c JOIN mdl_course_modules cm JO' at line 3

有人能推荐我吗？

Data for course_modules
id course moduletypeid
1    3     9 
2    3     9
3    3     17
4    5     20
5    5     18

Data for course_modules_completion
id  coursemoduleid userid
1      153           12
2      154           12
3      155           11
4      120           12
5      167            11

Answer 1

应该是

FROM mdl_course c 
         JOIN mdl_course_modules cm ON c.id = cm.course
         JOIN mdl_modules m ON cm.module = m.id
         JOIN mdl_course_modules_completion cmc ON 
           cmc.coursemoduleid = cm.id 
WHERE
           AND cmc.userid = 12 
           AND cm.course = 7 
           AND cmc.completionstate BETWEEN 1 AND 10

更新

您可以尝试去掉SELECT中的子查询，并将其替换为

SUM(if (cmc.completionstate BETWEEN 1 AND 10,1,0) AS "completedmodules"