我认为其他答案是正确的。
然而，我觉得你不是在寻找一个硬编码的列列表。如果这是您想要的，那么您正在寻找一个“pivot”sql语句。
在sqlserver（作为pivot）或postgresql（作为crosstab）中有一些实现，但在mysql或mariadb中我没有看到任何实现。

你可以找几个人来做家庭作业

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

首先，您确实应该使用正确的连接而不是where子句来进行连接：

SELECT student_number, homework_name, homework_points
FROM student join homework on student.studentid = homework.studentid

如果是你真正的问题，试试这个：

select student_name,
h1.homework_points as homework_1,
h2.homework_points as homework_2
from student s
left join homework h1
  on s.studentid = h1.studentid and h1.homework_name = 'Homework 1'
left join homework h2
  on s.studentid = h2.studentid and h2.homework_name = 'Homework 2'
where h1.homework_id is not null or h2.homework_id is not null

注意左连接的用法，因为某个学生可能只做了两个家庭作业中的一个。

使用案例

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

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