mysql - 查询以确定在物理，化学和数学中平均成绩最高的学生的学生证

This question already has answers here:

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

可以将其解释为第一行学生证101。他/她的物理成绩-93，化学成绩-63，数学成绩-74，历史成绩-29，哲学成绩-57
在我尝试过的查询下面。首先，我将marks列分为多个列。但是我无法确定在物理，化学和数学领域平均成绩最高的学生的ID。我无法获得输出。
预期产量：
物理学生证最高分-105
化学专业学生证最高分-104
数学学生证最高分-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;

最佳答案

您可以在下面的选项中进行检查-

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

关于mysql - 查询以确定在物理，化学和数学中平均成绩最高的学生的学生证，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58577328/