mysql - 如何在子查询中为每条记录的最大结果只获取一条记录

Question

我有一个用户-类(class)-考试数据库。

表结构:

用户表结构

+--------------------------------------------+
|                    user                    |
+--------------------------------------------+
| user_id | fullname     | email             |
+---------+--------------+-------------------+
| 1       | Test User 01 | test01@domain.com |
+---------+--------------+-------------------+
| 2       | Test User 02 | test02@domain.com |
+---------+--------------+-------------------+
| 3       | Test User 03 | test03@domain.com |
+---------+--------------+-------------------+
| 4       | Test User 04 | test04@domain.com |
+---------+--------------+-------------------+

类(class)表结构

+-----------------------+
|         course        |
+-----------------------+
| course_id | title     |
+-----------+-----------+
| 1         | Course 01 |
+-----------+-----------+
| 2         | Course 02 |
+-----------+-----------+
| 3         | Course 03 |
+-----------+-----------+

course_exam表结构(类(class)可以有一个或多个考试)

+----------------------------+
|         course_exam        |
+----------------------------+
| course_exam_id | course_id |
+----------------+-----------+
| 1              | 1         |
+----------------+-----------+
| 2              | 1         |
+----------------+-----------+
| 3              | 2         |
+----------------+-----------+
| 4              | 3         |
+----------------+-----------+
| 5              | 2         |
+----------------+-----------+

user_course_exam表结构(用户可以参加一个或多个考试)

+---------------------------------------------------------------------------------------------------------------+
|                                                user_course_exam                                               |
+---------------------------------------------------------------------------------------------------------------+
| user_course_exam_id | course_exam_id | user_id | right_answer_total | wrong_answer_total | blank_answer_total |
+---------------------+----------------+---------+--------------------+--------------------+--------------------+
| 1                   | 1              | 1       | 2                  | 3                  | 0                  |
+---------------------+----------------+---------+--------------------+--------------------+--------------------+
| 2                   | 1              | 1       | 4                  | 1                  | 0                  |
+---------------------+----------------+---------+--------------------+--------------------+--------------------+
| 3                   | 2              | 1       | 5                  | 0                  | 0                  |
+---------------------+----------------+---------+--------------------+--------------------+--------------------+
| 4                   | 1              | 1       | 3                  | 1                  | 1                  |
+---------------------+----------------+---------+--------------------+--------------------+--------------------+
| 5                   | 3              | 1       | 4                  | 0                  | 1                  |
+---------------------+----------------+---------+--------------------+--------------------+--------------------+

我应该准备如下报告:

• 用户编号
• 全名
• 电子邮件
• completed_course_total(用户完成考试的类(class)总数)
• remaining_course_total
• right_answer_total(最高分考试的最大正确答案。每门类(class)只能获取一个结果)

预期报告结果

+-------------------------------------------------------------------------------------------------------------------+
| user_id | fullname     | email             | completed_course_total | remaining_course_total | right_answer_total |
+---------+--------------+-------------------+------------------------+------------------------+--------------------+
| 1       | Test User 01 | test01@domain.com | 2                      | 1                      | 13                 |
+---------+--------------+-------------------+------------------------+------------------------+--------------------+
| 2       | Test User 02 | test02@domain.com | 0                      | 3                      | 0                  |
+---------+--------------+-------------------+------------------------+------------------------+--------------------+
| 3       | Test User 03 | test03@domain.com | 0                      | 3                      | 0                  |
+---------+--------------+-------------------+------------------------+------------------------+--------------------+
| 4       | Test User 04 | test04@domain.com | 0                      | 3                      | 0                  |
+---------+--------------+-------------------+------------------------+------------------------+--------------------+

这是我的查询，但正确答案返回所有考试。 我只想获得每门类(class)的最大正确答案总和。

请同时考虑这些情况:

• 一门类(class)可以有多个考试。
• 一个用户可以参加多个 course_exam。

SELECT DISTINCT 
  `user`.user_id,
    (   
        SELECT COUNT(DISTINCT(`user_course_exam`.`course_exam_id`)) FROM `user_course_exam` 
            INNER JOIN `course_exam` ON (`course_exam`.`course_exam_id` = `user_course_exam`.`course_exam_id`)
        WHERE `user_course_exam`.`user_id` = `user`.`user_id` && `course_exam`.course_id IN (
                SELECT course_id FROM course_exam 
                WHERE status = '1' 
                GROUP BY `course_exam`.`course_id`
            )
    ) AS completed_course,
    (   
        SELECT SUM(`user_course_exam`.`right_answer_total`) FROM `user_course_exam`
            INNER JOIN `course_exam` ON (`course_exam`.`course_exam_id` = `user_course_exam`.`course_exam_id`)
        WHERE `user_course_exam`.`user_id` = `user`.`user_id` && `course_exam`.course_id IN (
                SELECT course_id FROM course_exam
                WHERE status = '1' 
                GROUP BY `course_exam`.`course_id`
            ) ORDER BY `user_course_exam`.`right_answer_total` DESC
    ) AS right_answer
FROM
    `user`
WHERE
    `user`.`user_id` > 0
GROUP BY
    `user`.`user_id`
ORDER BY
    `user`.`user_id` ASC
LIMIT 15 OFFSET 0

JSFiddle:http://sqlfiddle.com/#!9/72ee15/1/0

Answer 1

您可以尝试使用 MAX() 而不是 SUM()

SELECT DISTINCT `user`.`user_id`,
    (
        SELECT COUNT(DISTINCT (`user_course_exam`.`course_exam_id`))
            FROM `user_course_exam`
                     INNER JOIN `course_exam`
                                ON (`course_exam`.`course_exam_id` = `user_course_exam`.`course_exam_id`)
            WHERE `user_course_exam`.`user_id` = `user`.`user_id` && `course_exam`.`course_id` IN (
                SELECT `course_id`
                    FROM `course_exam`
                    GROUP BY `course_exam`.`course_id`
            )
    ) AS `completed_course`,
    (
        SELECT sum(`all_curse`.`max_right_answer`)
            FROM (
                     SELECT MAX(`user_course_exam`.`right_answer_total`) AS `max_right_answer`,
                            `user_course_exam`.`user_id`
                         FROM `user_course_exam`
                                  INNER JOIN `course_exam`
                                             ON `course_exam`.`course_exam_id` = `user_course_exam`.`course_exam_id`
                         WHERE `course_exam`.`status` = 1
                         GROUP BY `course_exam`.`course_id`, `course_exam`.`course_exam_id`
                 ) AS `all_curse`
            WHERE `all_curse`.`user_id` = `user`.`user_id`
    ) AS `right_answer`
FROM `user`
WHERE `user`.`user_id` > 0
GROUP BY `user`.`user_id`
ORDER BY `user`.`user_id`
LIMIT 15 OFFSET 0

JSFiddle:Here