mysql - 需要一个查询以特定格式显示

Question

这个问题已经有答案了:

How can I return pivot table output in MySQL? (10 个回答)

已关闭 6 年前。

我有两张 table u和marklist :

SELECT * from u;
+------+-------+--------+-------+------------+
| id   | name  | adress | class | DOB        |
+------+-------+--------+-------+------------+
|    1 | Arun  | kollam |     6 | 1993-02-01 |
|    2 | Anoop | kollam |     6 | 1993-05-11 |
|    2 | Devi  | kollam |     6 | 1993-04-16 |
+------+-------+--------+-------+------------+

3 rows in set (0.02 sec)
 select * from marklist;
+------+-----------+------------+-------+--------+
| id   | subject   | markscored | outof | userid |
+------+-----------+------------+-------+--------+
|    1 | biology   |         37 |    50 |      1 |
|    2 | chemistry |         48 |    50 |      1 |
|    3 | physics   |         48 |    50 |      1 |
|    4 | biology   |         45 |    50 |      2 |
|    5 | chemistry |         41 |    50 |      2 |
|    6 | physics   |         34 |    50 |      2 |
|    7 | biology   |         39 |    50 |      3 |
|    8 | chemistry |         46 |    50 |      3 |
|    9 | physics   |         48 |    50 |      3 |
+------+-----------+------------+-------+--------+
9 rows in set (0.00 sec)

从这两个表中，我需要输出为:

+------+------+---------+-----------+---------+----------------+------------+
| name |class | biology | chemistry | physics | totlmarkscored | percentage |
+------+------+---------+-----------+---------+----------------+------------+

Answer 1

SELECT u.name, u.class, marklist.subject as biology, marklist.subject as chemistry, marklist.subject as physics, marklist.totlmarkscored, marklist.percentage
FROM u
INNER JOIN marklist
ON u.id=marklist.id

请随时修改我的错误!