SQLlite值到结果数组中

Question

我有两个SQL数据库表。如何从数据中获取结果数组？因为它应该是一个等级数组，它将进入标准的毁灭函数。据我所知，它应该是这样的[Alice'=>[5，4，5]，'Bob'=<[2] ]如果名字是唯一的，我需要每个人的成绩列表。准备句子已经完成，但问题在第一个foreach循环中。

Student table(id INTEGER PRIMARY KEY, name VARCHAR(255) UNIQUE);
Grade table(id INTEGER, grade INTEGER);
Student table     Grade table
id   name          id    grade
1   Alice           1      5
2   Bob             1      4
3   Carol           1      5
2      4

require_once 'functions.php';
$conn = getConnectionWithData('data.sql');
$stmt = $conn->prepare('select * from student inner join grade on grade.id = student.id' );
$stmt->execute();
$result = [];
foreach ($stmt as $row){
$name = $row['name'];
if(isset($result[$row['name']])){
$result[$name] = [$row['grade']];
}else{
$result[$name] = [$row['grade']];
}
}
foreach ($result as $name => $grades) {
$std = standardDeviation($grades);
print "$name: $stdn";

Answer 1

与您的代码非常相似，但不同的SQL:

-- SQLite syntax
select student.name, group_concat(grade.grade, ',') as grades
from student
inner join grade on grade.id = student.id
group by student.name

PHP代码：

require_once 'functions.php';
$conn = getConnectionWithData('data.sql');
// Extract grades as a comma-separated list of values per student in SQL
$sql = "select name, group_concat(grade, ',') grades from student inner join grade on grade.id = student.id group by name";
// There is no need to prepare a non-parameterized query
$results = $conn->query($sql);
foreach ($results as $result)
{
// Make an array out of the comma-separated list of values before calling standardDeviation
$std = standardDeviation(implode(',', $result['grades']));
print "{$result['name']}: $stdn";
}

Answer 2

您可以直接在查询中实现逻辑。SQL是一种基于集合的语言，在聚合数据方面非常有效。

在SQLite中，可以使用group_concat()进行字符串聚合。我发现子查询在这里很方便：

select s.*,
(
select '[' || group_concat(g.grade) || ']'
from grade g
where g.id = s.id
) as grades
from students s

如果你想筛选出完全没有成绩的学生，那么加入是一种更好的方法：

select s.*, g.grades
from students s
inner join (
select id, 
'[' || group_concat(grade) || ']' as grades
from grade g
group by id
) g on g.id = s.id