mysql> CREATE TABLE student
    -> (
    ->     ID INT,
    ->     student VARCHAR(20) CHARACTER SET UTF8,
    ->     course VARCHAR(20) CHARACTER SET UTF8,
    ->     mark INT
    -> ) ENGINE=MYISAM CHARACTER SET UTF8;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT student VALUES(1,'张三','语文',90);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT student VALUES(2,'李四','语文',80);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT student VALUES(3,'张三','数学',70);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT student VALUES(4,'李四','数学',80);
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT
    ->     student,
    ->     SUM(CASE WHEN course = '语文' THEN mark ELSE 0 END) AS 语文,
    ->     SUM(CASE WHEN course = '数学' THEN mark ELSE 0 END) AS 数学
    -> FROM student
    -> GROUP BY student;
+---------+------+------+
| student | 语文 | 数学 |
+---------+------+------+
| 张三    |   90 |   70 |
| 李四    |   80 |   80 |
+---------+------+------+
2 rows in set (0.00 sec)

mysql>
mysql> SELECT
    ->     student,
    ->     SUM(IF(course='语文',mark,0)) AS 语文,
    ->     SUM(IF(course='数学',mark,0)) AS 数学
    -> FROM student
    -> GROUP BY student;
+---------+------+------+
| student | 语文 | 数学 |
+---------+------+------+
| 张三    |   90 |   70 |
| 李四    |   80 |   80 |
+---------+------+------+
2 rows in set (0.00 sec)

mysql>
mysql> DROP TABLE student;
Query OK, 0 rows affected (0.00 sec)