Mysql子查询从数据库中获取多条记录时返回错误 - Mysql subquery return error when more than one record is fetched from db

我使用下面的代码生成学生在不同科目的成绩列表，并根据他们的表现显示所有学生。

 $qry = "select * from (
 select (select percentage from exams_db where subjecttype = 'English') as English,
           (select percentage from exams_db where subjecttype = 'Biology') as Biology,
           (select percentage from exams_db where subjecttype = 'Maths') as Maths,
           (select percentage from exams_db where subjecttype = 'Science') as Science,
           (select percentage from exams_db where subjecttype = 'SocialStudies') as SocialStudies,
           (select total_marks from exams_db ORDER BY total_marks DESC LIMIT 1) as total_marks,
           (select exam_date from exams_db ORDER BY id DESC LIMIT 1) as exam_date
    from exams_db group by Maths
    union all
    select (select grade from exams_db where subjecttype = 'English') as English,
           (select grade from exams_db where subjecttype = 'Biology' ) as Biology,
           (select grade from exams_db where subjecttype = 'Maths') as Maths,
           (select grade from exams_db where subjecttype = 'Science') as Science,
           (select grade from exams_db where subjecttype = 'SocialStudies') as SocialStudies, 
           (select total_marks from exams_db  ORDER BY total_marks DESC LIMIT 1) as total_marks,
           (select exam_date from exams_db ORDER BY id DESC LIMIT 1) as exam_date
    from exams_db group by Maths
     ) t ";

然而，我得到一个错误，子查询返回多于1行

但是，当我像下面这样编辑代码时，它只对一个用户有效。

$qry = "select * from (
 select (select percentage from exams_db  where subjecttype = 'English' AND user = '$userid') as English,
           (select percentage from exams_db  where subjecttype = 'Biology' AND user = '$userid') as Biology,
           (select percentage from exams_db  where subjecttype = 'Maths' AND user = '$userid') as Maths,
           (select percentage from exams_db  where subjecttype = 'Science' AND user = '$userid') as Science,
           (select percentage from exams_db  where subjecttype = 'SocialStudies' AND user = '$userid') as SocialStudies,
           (select total_marks from exams_db  WHERE user = '$userid' ORDER BY total_marks DESC LIMIT 1) as total_marks,
           (select exam_date from exams_db  WHERE user = '$userid' ORDER BY id DESC LIMIT 1) as exam_date
    from exams_db  group by Maths
    union all
    select (select grade from exams_db  where subjecttype = 'English' AND user = '$userid') as English,
           (select grade from exams_db  where subjecttype = 'Biology' AND user = '$userid') as Biology,
           (select grade from exams_db  where subjecttype = 'Maths' AND user = '$userid') as Maths,
           (select grade from exams_db  where subjecttype = 'Science' AND user = '$userid') as Science,
           (select grade from exams_db  where subjecttype = 'SocialStudies' AND user = '$userid') as SocialStudies, 
           (select total_marks from exams_db WHERE user = '$userid' ORDER BY total_marks DESC LIMIT 1) as total_marks,
           (select exam_date from exams_db  WHERE user = '$userid' ORDER BY id DESC LIMIT 1) as exam_date
    from exams_db  group by Maths
     ) t ";

但是，我想显示所有的用户。那么，我如何编辑我的第一个代码，使我能够显示所有的用户，因为第二个代码帮助了我，它只允许我显示一个用户。

我的数据库结构如下

user    subjecttype  percentage  grade
 109283  English       40%         B
 345245  Biology       80%         A
 832904  Science       50%         C

我不是100%确定你想在这里输出什么。

但是，假设您想要所有用户及其百分比的列表，以及所有用户的所有分数，以及该用户的最后一次考试日期和最高总分:-

SELECT exam_date.user, English.percentage, Biology.percentage, Maths.percentage, Science.percentage, SocialStudies.percentage, exam_date.ExamDate, total_marks.TotalMarks
FROM (select user, MAX(exam_date) AS ExamDate from exams_db  WHERE user = '$userid' GROUP BY user) as exam_date
LEFT OUTER JOIN (select user, percentage from exams_db  where subjecttype = 'English' GROUP BY user) as English ON exam_date.user = English.user
LEFT OUTER JOIN (select user, percentage from exams_db  where subjecttype = 'Biology' GROUP BY user) as Biology ON exam_date.user = Biology.user
LEFT OUTER JOIN (select user, percentage from exams_db  where subjecttype = 'Maths' GROUP BY user) as Maths ON exam_date.user = Maths.user
LEFT OUTER JOIN (select user, percentage from exams_db  where subjecttype = 'Science' GROUP BY user) as Science ON exam_date.user = Science.user
LEFT OUTER JOIN (select user, percentage from exams_db  where subjecttype = 'SocialStudies' GROUP BY user) as SocialStudies ON exam_date.user = SocialStudies.user
LEFT OUTER JOIN (select user, MAX(total_marks) AS TotalMarks from exams_db  WHERE user = '$userid' GROUP BY user) as total_marks ON exam_date.user = total_marks.user
UNION ALL
SELECT exam_date.user, English.percentage, Biology.percentage, Maths.percentage, Science.percentage, SocialStudies.percentage, exam_date.ExamDate, total_marks.TotalMarks
FROM (select user, MAX(exam_date) AS ExamDate from exams_db  WHERE user = '$userid' GROUP BY user) as exam_date
LEFT OUTER JOIN (select user, grade from exams_db  where subjecttype = 'English' GROUP BY user) as English ON exam_date.user = English.user
LEFT OUTER JOIN (select user, grade from exams_db  where subjecttype = 'Biology' GROUP BY user) as Biology ON exam_date.user = Biology.user
LEFT OUTER JOIN (select user, grade from exams_db  where subjecttype = 'Maths' GROUP BY user) as Maths ON exam_date.user = Maths.user
LEFT OUTER JOIN (select user, grade from exams_db  where subjecttype = 'Science' GROUP BY user) as Science ON exam_date.user = Science.user
LEFT OUTER JOIN (select user, grade from exams_db  where subjecttype = 'SocialStudies' GROUP BY user) as SocialStudies ON exam_date.user = SocialStudies.user
LEFT OUTER JOIN (select user, MAX(total_marks) AS TotalMarks from exams_db  WHERE user = '$userid' GROUP BY user) as total_marks ON exam_date.user = total_marks.user

如果这不是你想要的，你能不能用代码解释一下?你们有学生桌吗?

EDIT -对于已经完成考试的用户列表，我认为下面可能会这样做:-

SELECT exam_date.user, 
    English.percentage, 
    Biology.percentage, 
    Maths.percentage, 
    Science.percentage, 
    SocialStudies.percentage, 
    English.grade, 
    Biology.grade, 
    Maths.grade, 
    Science.grade, 
    SocialStudies.grade, 
    exam_date.ExamDate, 
    total_marks.TotalMarks
FROM (SELECT user, MAX(exam_date) AS ExamDate FROM exams_db GROUP BY user) as exam_date
LEFT OUTER JOIN (SELECT user, percentage, grade FROM exams_db where subjecttype = 'English' GROUP BY user) as English ON exam_date.user = English.user
LEFT OUTER JOIN (SELECT user, percentage, grade FROM exams_db where subjecttype = 'Biology' GROUP BY user) as Biology ON exam_date.user = Biology.user
LEFT OUTER JOIN (SELECT user, percentage, grade FROM exams_db where subjecttype = 'Maths' GROUP BY user) as Maths ON exam_date.user = Maths.user
LEFT OUTER JOIN (SELECT user, percentage, grade FROM exams_db where subjecttype = 'Science' GROUP BY user) as Science ON exam_date.user = Science.user
LEFT OUTER JOIN (SELECT user, percentage, grade FROM exams_db where subjecttype = 'SocialStudies' GROUP BY user) as SocialStudies ON exam_date.user = SocialStudies.user
LEFT OUTER JOIN (SELECT user, MAX(total_marks) AS TotalMarks FROM exams_db GROUP BY user) as total_marks ON exam_date.user = total_marks.user

注意我对total_marks字段有点怀疑(您的原始查询只是获得最高的总分)。

可能更有效(但完全未经测试)是:-

SELECT users.user, 
    exam_dates.ExamDate, 
    total_marks.TotalMarks, 
    MAX(IF(Sub1.subjecttype='English', exams_db.percentage, 0)) AS EnglishPercentage,
    MAX(IF(Sub1.subjecttype='English', exams_db.grade, 0)) AS EnglishGrade,
    MAX(IF(Sub1.subjecttype='Biology', exams_db.percentage, 0)) AS BiologyPercentage,
    MAX(IF(Sub1.subjecttype='Biology', exams_db.grade, 0)) AS BiologyGrade,
    MAX(IF(Sub1.subjecttype='Maths', exams_db.percentage, 0)) AS MathsPercentage,
    MAX(IF(Sub1.subjecttype='Maths', exams_db.grade, 0)) AS MathsGrade,
    MAX(IF(Sub1.subjecttype='Science', exams_db.percentage, 0)) AS SciencePercentage,
    MAX(IF(Sub1.subjecttype='Science', exams_db.grade, 0)) AS ScienceGrade,
    MAX(IF(Sub1.subjecttype='SocialStudies', exams_db.percentage, 0)) AS SocialStudiesPercentage,
    MAX(IF(Sub1.subjecttype='SocialStudies', exams_db.grade, 0)) AS SocialStudiesGrade
FROM users
LEFT OUTER JOIN (
    SELECT user, subjecttype, MAX(exam_date) AS LatestSubjectExam 
    FROM exams_db 
    WHERE subjecttype IN ('English', 'Biology', 'Maths', 'Science', 'SocialStudies') 
    GROUP BY user, subjecttype
) Sub1 ON users.user = Sub1.user
LEFT OUTER JOIN exams_db ON Sub1.user = exams_db.user AND Sub1.subjecttype = exams_db.subjecttype AND Sub1.LatestSubjectExam = exams_db.exam_date
LEFT OUTER JOIN (SELECT user, MAX(total_marks) AS TotalMarks FROM exams_db GROUP BY user) total_marks ON users.user = total_marks.user
LEFT OUTER JOIN (SELECT user, MAX(exam_date) AS ExamDate FROM exams_db GROUP BY user) exam_dates ON users.user = exam_dates.user
GROUP BY users.user, exam_dates.ExamDate, total_marks.TotalMarks

编辑再次

尝试在第二种查询方式中处理重复的用户/考试

SELECT exam_date.user, 
    English.percentage, 
    Biology.percentage, 
    Maths.percentage, 
    Science.percentage, 
    SocialStudies.percentage, 
    English.grade, 
    Biology.grade, 
    Maths.grade, 
    Science.grade, 
    SocialStudies.grade, 
    exam_date.ExamDate, 
    total_marks.TotalMarks
FROM (SELECT user, MAX(exam_date) AS ExamDate FROM exams_db GROUP BY user) as exam_date
LEFT OUTER JOIN (
    SELECT user, subjecttype, MAX(exam_date) AS LatestSubjectExam 
    FROM exams_db 
    WHERE subjecttype IN ('English', 'Biology', 'Maths', 'Science', 'SocialStudies') 
    GROUP BY user, subjecttype
) Sub1 ON exam_date.user = Sub1.user
LEFT OUTER JOIN (SELECT user, exam_date, percentage, grade FROM exams_db where subjecttype = 'English' GROUP BY user, exam_date) as English ON exam_date.user = English.user AND Sub1.subjecttype = 'English' AND Sub1.LatestSubjectExam = English.exam_date
LEFT OUTER JOIN (SELECT user, exam_date, percentage, grade FROM exams_db where subjecttype = 'Biology' GROUP BY user, exam_date) as Biology ON exam_date.user = Biology.user AND Sub1.subjecttype = 'Biology' AND Sub1.LatestSubjectExam = Biology.exam_date
LEFT OUTER JOIN (SELECT user, exam_date, percentage, grade FROM exams_db where subjecttype = 'Maths' GROUP BY user, exam_date) as Maths ON exam_date.user = Maths.user AND Sub1.subjecttype = 'Maths' AND Sub1.LatestSubjectExam = Maths.exam_date
LEFT OUTER JOIN (SELECT user, exam_date, percentage, grade FROM exams_db where subjecttype = 'Science' GROUP BY user, exam_date) as Science ON exam_date.user = Science.user AND Sub1.subjecttype = 'Science' AND Sub1.LatestSubjectExam = Science.exam_date
LEFT OUTER JOIN (SELECT user, exam_date, percentage, grade FROM exams_db where subjecttype = 'SocialStudies' GROUP BY user, exam_date) as SocialStudies ON exam_date.user = SocialStudies.user AND Sub1.subjecttype = 'SocialStudies' AND Sub1.LatestSubjectExam = SocialStudies.exam_date
LEFT OUTER JOIN (SELECT user, MAX(total_marks) AS TotalMarks FROM exams_db GROUP BY user) as total_marks ON exam_date.user = total_marks.user