SQL查询将许多关系填充为具有条件的数组类型的字段

Question

假设我有以下表格:

学生

ID --- Name
A --- Andy
B --- Billy
C --- Chris

得分

StudentID  --- Subject --- Score
A --- Math --- 6
A --- English --- 5
B --- Math --- 8
B --- English--- 5
C --- Math --- 1
C --- English --- 4

然后我想查询至少有一个分数=== 5的学生，并填充他们的所有分数。

在上面的例子中，我想要这个结果

[
{
id: "A",
name: "Andy",
scores: [
{
subject: "Math",
score: 6
},
{
subject: "English",
score: 5
}
]
},
{
id: "B",
name: "Billy",
scores: [
{
subject: "Math",
score: 8
},
{
subject: "English",
score: 5
}
]
}
]

我想知道ORM如何在原始SQL查询(SELECT FROM WHERE)中查询此结果。我很感激任何帮助，提前谢谢。

Answer 1

我不确定你想要什么结果。但是思想是JOIN和聚合:

select s.id, s.name,
group_concat(sc.subject, ':', score) as subject_scores
from students s join
scores sc
on s.id = sc.studentid
group by s.id, s.name
having sum(sc.score = 5) > 0;

或者如果5是最高分，则:

having sum(sc.score) = 5

如果你想要JSON格式的结果集，那么显然你应该使用JSON函数而不是字符串函数。