mysql - 查询物理、化学、数学平均分最高的学生id

Question

这个问题已经有答案了:

Is storing a delimited list in a database column really that bad? (10 个回答)

已关闭 3 年前。

我有一个名为 marks 的表有两列student_id和marks 。 student_id列包含学生的唯一 ID marks列包含学生五个不同科目的分数，以逗号分隔。我尝试了多种方法来确定物理、化学和数学平均分最高的学生的id。我无法得到我的输出。有人可以帮忙吗？数据如下所示:表-marks

Student Id | marks
101        | 93,63,74,29,57
102        | 63,66,34,89,27
103        | 93,64,54,22,97
104        | 43,93,34,78,87
105        | 95,83,54,49,78

这可以解释为第一行学生 ID 101。他/她的物理分数 - 93，化学分数 - 63，数学分数 - 74，历史 - 29，哲学 - 57下面是我尝试过的查询。首先，我将标记列分成多列。但我无法确定物理、化学和数学平均分最高的学生的 ID。我无法得到我的输出。预期输出:物理最高分学生 ID - 105化学最高分学生 ID - 104数学最高分学生 ID - 101

输出应仅显示前三门科目平均分最高的学生

DELIMITER $$
CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255) DETERMINISTIC
BEGIN 
    RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
END$$

DELIMITER ;
SELECT student_id,SPLIT_STR(marks, ',', 1) as physics,
     SPLIT_STR(marks, ',', 2) as chemistry,
     SPLIT_STR(marks, ',', 3) as mathematics,
      SPLIT_STR(marks, ',', 4) as history ,
      SPLIT_STR(marks, ',', 5) as philosophy 
FROM   marks;

Answer 1

您可以检查下面的选项-

SELECT D.*,E.`Student Id` FROM 
(
    SELECT * FROM
    (
        SELECT Subject,MAX(Marks) Max_Marks FROM
        (
            SELECT `Student Id`, 'physics' Subject,
            SUBSTRING_INDEX(marks, ',', 1) Marks
            FROM your_table A

            UNION ALL

            SELECT `Student Id`, 'chemistry' Subject,
            SUBSTRING_INDEX(REPLACE(marks,concat(SUBSTRING_INDEX(marks, ',', 1),','),''), ',', 1) Marks
            FROM your_table A

            UNION ALL

            SELECT `Student Id`, 'mathematics' Subject,
            SUBSTRING_INDEX(REPLACE(marks,concat(SUBSTRING_INDEX(marks, ',', 2),','),''), ',', 1) Marks
            FROM your_table A
        )B
        GROUP BY Subject
    )C
) D
INNER JOIN 

(
    SELECT `Student Id`, 'physics' Subject,
    SUBSTRING_INDEX(marks, ',', 1) Marks
    FROM your_table A

    UNION ALL

    SELECT `Student Id`, 'chemistry' Subject,
    SUBSTRING_INDEX(REPLACE(marks,concat(SUBSTRING_INDEX(marks, ',', 1),','),''), ',', 1) Marks
    FROM your_table A

    UNION ALL

    SELECT `Student Id`, 'mathematics' Subject,
    SUBSTRING_INDEX(REPLACE(marks,concat(SUBSTRING_INDEX(marks, ',', 2),','),''), ',', 1) Marks
    FROM your_table A
)E ON D.Subject = E.Subject AND D.Max_Marks = E.Marks;

输出是-

chemistry   93  104
mathematics 74  101
physics     95  105