3个表之间连接的MySQL中值

Question

我有三个表( teacher，course_work和tables )，我想计算每个教师的中位数。我看到了一个关于中位数计算的查询，我试图调整它，但没有成功。

我现在的查询如下：

SELECT
    dd.id,
    AVG(dd.score) as median_val
FROM (
SELECT d.id, d.score, @rownum := @rownum + 1 as row_number, @total_rows := @rownum
FROM (
        SELECT
            teacher.id,
            submission.id AS submission_id,
            submission.score
        FROM teacher
        LEFT JOIN course_work ON teacher.id = course_work.teacher_id
        LEFT JOIN submission ON course_work.id = submission.course_work_id
        ORDER BY teacher.id
) d, (SELECT @rownum := 0) r
WHERE d.score is NOT NULL
ORDER BY d.score
) as dd
WHERE dd.row_number IN (FLOOR((@total_rows + 1) / 2), FLOOR((@total_rows + 2) / 2))
GROUP BY dd.id;

非常感谢。

SQL模式如下：

https://pastebin.com/RT9iWJPH

Answer 1

这可能会给出你正在寻找的结果。你能在你的数据上测试它吗？

SELECT
    t.id as teacherid,
    t.full_name teacherfullname,
    COUNT(*) AS total_rows,
IF
    (
        count(*)% 2 = 1,
        CAST(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT( s.score ORDER BY s.score SEPARATOR ',' ),
                    ',',
                50 / 100 * COUNT(*)),
                ',',
                - 1 
            ) AS DECIMAL 
        ),
        ROUND((
                CAST(
                    SUBSTRING_INDEX(
                        SUBSTRING_INDEX( GROUP_CONCAT( s.score ORDER BY s.score SEPARATOR ',' ), ',', 50 / 100 * COUNT(*) + 1 ),
                        ',',
                        - 1 
                    ) AS DECIMAL 
                    ) + CAST(
                    SUBSTRING_INDEX(
                        SUBSTRING_INDEX(
                            GROUP_CONCAT( s.score ORDER BY s.score SEPARATOR ',' ),
                            ',',
                        50 / 100 * COUNT(*)),
                        ',',
                        - 1 
                    ) AS DECIMAL 
                )) / 2 
        )) AS median 
FROM
    teacher t
    JOIN course_work cw ON cw.teacher_id = t.id
    JOIN submission s ON cw.id = s.course_work_id 
WHERE
    s.score IS NOT NULL 
GROUP BY
    teacherid 
ORDER BY
    s.score