Question

I m trying to make a list of students and their efficiency with tests. In MySql database I have tables

<代码>用户——学生一览表

id | name
_________
1  | Joe
2  | Marry
3  | Max
4  | Anna
----------

courses - table with courses

id | name
_____________
1  | Course 1
2  | Course 2
----------

<代码>questions - table with questions for each course. Row cours_id 指出,这个问题在哪一过程中属于问题。

id | cours_id | question
_________________________________
1  | 1        | Course 1 - question 1
2  | 1        | Course 1 - question 2
3  | 1        | Course 1 - question 3
4  | 1        | Course 1 - question 4
5  | 2        | Course 2 - question 1
6  | 2        | Course 2 - question 2
7  | 2        | Course 2 - question 3
8  | 2        | Course 2 - question 4

cours_invitations - every student gets invitation for a course. Row user_id shows the id of user who is invited to do a course. cours_id represents the id of the course which a student should do. When row status has value 0 that means that a student didn t started with the course (pending), and if it has value 1 that means that the student has started it (or done).

id | user_id | cours_id | status
________________________________
1  | 1       | 1        | 1
2  | 1       | 2        | 0
3  | 2       | 1        | 0
4  | 3       | 1        | 1
5  | 4       | 1        | 1
6  | 4       | 2        | 1

<>>Example: 请Joe和Anna参加第1课程和第2课程,邀请Marry和Max完成课程 1. 导言 Joe修完了1门课程,但不是2门课程,Marry没有做任何事,Max没有课程。

courses_stats - are the statistics of the courses questions which students have done. Status represent the accuracy of the answer. 0 stands for wrong answer and 1 for correct.

id | user_id | question_id | status

___________________________________
1  | 1       | 1           | 1
2  | 1       | 2           | 1
3  | 1       | 3           | 0
4  | 2       | 1           | 1
5  | 2       | 2           | 1
6  | 2       | 3           | 1
7  | 2       | 4           | 1
8  | 4       | 1           | 1
9  | 4       | 2           | 1
10 | 4       | 3           | 0
11 | 4       | 4           | 0
12 | 4       | 5           | 1
13 | 4       | 6           | 1

<>>Example: 在第一期课程中,Joe提出了3个问题。通知说,他没有从那一过程中回答所有问题,那是不正确的。

Max女士回答了所有问题,Anna从一开始就提出了所有问题(一半不正确),一半从二次来看(完全正确)。

I need a query with sudents names, perecentage of finished courses, percentage of correct answers of those courses which they did (not all courses) and possibility to order students by those percentages. Something like this:

name  | completed courses | completed questions
______________________________________________
Max   |100%               |100%
Anna  |100%               |50%
Joe   |50%                |50%
Marry |0%                 |0%

甚至有可能这样做吗? 我是否需要在表格中增加这方面的数据?

Answer 1

我认为,这应该是你们所需要的:

SELECT
    users.name,
    CONCAT(COUNT(
        DISTINCT CASE
        WHEN cours_invitations.status = 1 THEN
            cours_invitations.id
        ELSE
            NULL
        END
    ) / COUNT(
        DISTINCT cours_invitations.id
    ) * 100,  % ) AS completed_courses,
    CONCAT(COUNT(
        DISTINCT CASE
        WHEN courses_stats.status = 1 THEN
            courses_stats.id
        ELSE
            NULL
        END
    ) / COUNT(DISTINCT questions.id) * 100,  % ) AS completed_questions
FROM
    users
LEFT JOIN cours_invitations ON cours_invitations.user_id = users.id
LEFT JOIN questions ON cours_invitations.cours_id = questions.cours_id
AND cours_invitations.status = 1
LEFT JOIN courses_stats ON users.id = courses_stats.user_id
GROUP BY
    users.id
ORDER BY
    completed_courses DESC,
    completed_questions DESC

作为向您提出的问题,为什么是称为cours_*的表格。而不是<条码>

Answer 2

Here you can find with table schemas and sample data, and result of query. MichaelRushton has perfect answer but course_stats has to be LEFT join I think. Because if student has cours_invitations but no course_stats, that query will not return that user.

http://sqlfiddle.com/#!2/019dc/1。

SELECT
    users.name,
    COUNT(
        DISTINCT CASE
        WHEN course_invitations.status = 1 THEN
            course_invitations.id
        ELSE
            NULL
        END
    ) / COUNT(
        DISTINCT course_invitations.id
    ) * 100 AS completed_courses,
    COUNT(
        DISTINCT CASE
        WHEN courses_stats.status = 1 THEN
            courses_stats.id
        ELSE
            NULL
        END
    ) / COUNT(DISTINCT questions.id) * 100 AS completed_questions
FROM users
INNER JOIN course_invitations ON course_invitations.user_id = users.id
INNER JOIN questions ON course_invitations.cours_id = questions.cours_id
LEFT JOIN courses_stats ON users.id = courses_stats.user_id
GROUP BY
    users.id

<>Result>

NAME    COMPLETED_COURSES   COMPLETED_QUESTIONS
Joe 50  25
Marry   0   100
Max 100 0
Anna    100 50

Answer 3

我猜测你指的是“正确答案的百分比”。缩略语例如,该法典

count(distinct if(cours_invitations.status and courses_stats.status, 
                      NULL, questions.id))

标注满足以下条件(cours_invitations.status/code>的(单独)问题数目。采用这一trick计,整个问题是简单而棘手的:



select users.name, 
    count(distinct if(cours_invitations.status, 
                         NULL, 
                         cours_invitations.cours_id)) 
        / count(distinct cours_invitations.cours_id) 
        * 100 as courses_completed,
    count(distinct if(cours_invitations.status and courses_stats.status, 
                          NULL, 
                          questions.id)) 
        / count(distinct if(cours_invitations.status, 
                               NULL, 
                               questions.id)) 
        * 100 as correct_answers
from users
    left join cours_invitations on users.id = cours_invitations.user_id
    left join questions using (cours_id)
    left join courses_stats on users.id = courses_stats.user_id 
                               and questions.id = courses_stats.question_id
group by users.id
order by correct_answers


我建议增加我方言之外的比例,因为这个比例远比我方略高,这将给我方格的争.增加令人不安的复杂性。

友情链接