如何在SurrealDB中按关系输出分组，获取学生各科目最高分？

场景说明 #

有2名学生（A和B）和3门科目（X、Y、Z），学生可多次参加同一科目的测试，我们仅关注每门科目的最高分。

数据库初始化语句 #

> use NS stackoverflow

stackoverflow> use DB example

stackoverflow/example> CREATE student:A;
stackoverflow/example> CREATE subject:X;
stackoverflow/example> CREATE subject:Y;
stackoverflow/example> CREATE subject:Z;
stackoverflow/example> RELATE student:A->scores->subject:X SET score = 4;
stackoverflow/example> RELATE student:A->scores->subject:X SET score = 6;
stackoverflow/example> RELATE student:A->scores->subject:Y SET score = 9;
stackoverflow/example> RELATE student:A->scores->subject:Y SET score = 8;
stackoverflow/example> RELATE student:A->scores->subject:Z SET score = 5;
stackoverflow/example> CREATE student:B;
stackoverflow/example> RELATE student:B->scores->subject:X SET score = 6;
stackoverflow/example> RELATE student:B->scores->subject:X SET score = 8;
stackoverflow/example> RELATE student:B->scores->subject:Y SET score = 2;
stackoverflow/example> RELATE student:B->scores->subject:Z SET score = 42;

当前已实现的查询 #

目前已能通过以下查询获取每个学生的所有科目最高分：

stackoverflow/example> select id, math::max(->scores.score) from student;
[[{ id: student:A, "math::max": 9 }, { id: student:B, "math::max": 42 }]]

需求与问题 #

需要实现获取每个学生每门科目的最高分，期望返回结果如下：

[[
  {
    id: student:A,
    "->scores": [
      {
        out: subject:X,
        score: 6
      },
      {
        out: subject:Y,
        score:9
      },
      {
        out: subject:Z,
        score: 5
      }
    ]
  },
  {
    id: student:B,
    "->scores": [
      {
        out: subject:X,
        score: 8
      },
      {
        out: subject:Y,
        score: 2
      },
      {
        out: subject:Z,
        score: 42
      }
    ]
  }
]]

解决方案 #

使用group by结合math::max按科目分组，通过嵌套查询关联到每个学生，执行以下语句即可得到期望结果：

stackoverflow/example> select id, (select out, math::max(score) as score from ->scores group by out) as ->scores from student;

内容的提问来源于stack exchange，提问作者Mouradif