mysql case when 返回多个字段

当使用mysql的case when语句返回多个字段时，可以使用嵌套查询或连接查询。以下是两种方法的示例代码：

1. 使用嵌套查询

SELECT
a.id, a.name, a.age, b.avg_score, b.max_score
FROM
student a
INNER JOIN
(SELECT id, AVG(score) as avg_score, MAX(score) as max_score FROM score GROUP BY id) b
ON
a.id = b.id;

2. 使用连接查询

SELECT
a.id, a.name, a.age, AVG(b.score) as avg_score, MAX(b.score) as max_score
FROM
student a
LEFT JOIN
score b
ON
a.id = b.id
GROUP BY
a.id;