mysql : get count and percent for all entries of a table with given conditions

Question

假设我们有以下模型:

• 练习表(id、名称)
• 用户表(ID、姓名、电子邮件)
• 考试表(id、user_id、日期)

• 考试和练习之间的连接表(exam_id、exercise_id)

• 每个用户每天可以进行一次考试。

• 每项考试都由许多练习组成(具有且属于许多练习)。

对于每个用户(登录时)，我想显示一个表格，其中显示所有练习以及用户在给定时间段内进行的每个考试的计数和百分比(使用考试的日期属性)。

此时，我可以使用以下查询显示用户在给定时间段内进行的所有练习及其百分比。

    SELECT 
       o.user_id,
       o.exercise_id, 
       o.exercise_name, 
       COUNT(*) AS nb_exams,
       (COUNT(*) * 100 / (
           SELECT COUNT(*)
           FROM exams
              LEFT JOIN users ON exams.user_id = users.id 
           WHERE users.id = 1 AND exams.day >= "2015-09-01" AND exams.day <= "2015-09-07"
       )) AS percent
    FROM (
        SELECT
            exercises.id AS exercise_id,
            exercises.name AS exercise_name, 
            exams.id AS exam_id, 
            users.id AS user_id
        FROM exercises 
            LEFT JOIN exercises_exams ON exercises_exams.exercise_id = exercises.id
            LEFT JOIN exams ON exercises_exams.exam_id = exams.id 
            LEFT JOIN users ON exams.user_id = users.id 
        WHERE users.id = 1 AND exams.day >= "2015-09-01" AND exams.day <= "2015-09-07"
        GROUP BY exercise_id,exam_id
    ) AS o
    GROUP BY exercise_id;

但我还想以 0 的值显示他尚未做的练习。

是否可以通过 mysql 的一个查询来完成此操作？

编辑-

这是sqlfiddle对于当前查询。您会注意到，仅返回考试中使用的 3 个练习。我想列出所有带有计数和百分比的练习(甚至第四个带有 0)

Answer 1

首先，您应该删除内部查询中 join 子句的 where 条件，以便在练习未完成时看到 0 结果执行。获取与 0 结果对应的 user_id 是没有意义的。这是我能想到的最接近的解决方案。

Fiddle

SELECT 
   o.user_id,
   o.exercise_id, 
   o.exercise_name, 
   sum(case when exam_id is null then 0 else 1 end) AS nb_exams,
   (sum(case when exam_id is null then 0 else 1 end) * 100 / (
       SELECT COUNT(*)
       FROM exams
       LEFT JOIN users ON exams.user_id = users.id 
     where exams.day >= "2015-09-01" AND exams.day <= "2015-09-07"
   )) AS percent
FROM (
    SELECT
        exercises.id AS exercise_id,
        exercises.name AS exercise_name, 
        exams.id AS exam_id , 
        users.id AS user_id
    FROM exercises 
        LEFT JOIN exercises_exams ON exercises_exams.exercise_id = exercises.id
        LEFT JOIN exams ON exercises_exams.exam_id = exams.id 
        and exams.day >= "2015-09-01" AND exams.day <= "2015-09-07"
        LEFT JOIN users ON exams.user_id = users.id 
) AS o
GROUP BY exercise_id,exercise_name