数据库SQL查询按第一列对结果进行分组

Question

我需要查询的帮助，我想按前两列对结果行进行分组，但我不知道如何继续。

我的问题

 SELECT student_number, homework_name, homework_points 
   FROM student, homework 
   WHERE student.studentid = homework.studentid

下面是我的查询结果

student_number  homework_name  homework_points
--------------  -------------  ---------------
7554            Homework 1     22.5 
7554            Homework 2     16.0
7677            Homework 1     36.0
7677            Homework 2     14.5

如何对查询进行转换以获得如下结果

student_number  Homework 1     Homework 2
--------------  -------------  ---------------
7554            22.5           16.0 
7677            36.0           14.5

任何帮助都将不胜感激。

谢谢!

Answer 1

用例：

        SELECT student_number, 
        max(case when homework_name='Homework 1' then homework_points end) as Homework1,
       max(case when homework_name='Homework 2' then homework_points end) as Homework2
        FROM student inner join homework 
       on student.studentid = homework.studentid 
      group by student_number

http://sqlfiddle.com/#!9/c33289/2

id    Homework1  Homework2
7554    22.5     16
7677    36       14

Answer 2

SELECT s.student_number, 
       sum(case when h.homework_name = 'Homework 1' then h.homework_points else 0 end) as Homework_1,
       sum(case when h.homework_name = 'Homework 2' then h.homework_points else 0 end) as Homework_2
FROM student s
JOIN homework h ON s.studentid = h.studentid
GROUP BY s.student_number

Answer 3

你可以用几个join来做桌子作业。

   SELECT s.student_number
      , a.homework_points AS  `Homework 1`
      ,  b.homework_points AS  `Homework 21`
   FROM student s
   LEFT JOIN homework a ON s.studentid = a.studentid 
          and a.homework_name =' Homework 1' 
   left JOIN homework b ON s.studentid = b.studentid 
        and b.homework_name =' Homework 2'
    ORDER BY s.student_number