php - 复杂的mysql查询来创建html表

我为一个项目做了这个mock-up，现在我必须用php来做，但是我不知道如何从数据库中获取数据以便创建html表。
以下是相关表格的定义：
等级：

grade_id | title        | sort_order
-------------------------------
   1     | Preschool    |    10
   2     | PreK         |    20
   3     | Kindergarten |    30
   4     | Grade 1      |    40
   5     | Grade 2      |    50
   6     | Grade 3      |    60

能力集：

competency_set_id | title                   | sort_order
--------------------------------------------------------
   1              | Grouping and sequencing |    10
   2              | Conecting: orientation  |    20
   3              | Conecting: reference    |    30

能力：

competency_id | competency_set_id | grade_id | sort_order | title
--------------------------------------------------------------------------------------------------
   1          |          1        |     1    |     10     | Procedure / Recount / Narrative Retell
   2          |          1        |     2    |     10     | Procedure preK
   3          |          1        |     3    |     10     | Procedure kindergarten
   4          |          1        |     4    |     10     | Procedure Grade 1
   5          |          1        |     5    |     10     | Procedure Grade 2
   6          |          1        |     6    |     10     | Procedure Grade 3
   7          |          1        |     2    |     20     | Recount / Narrative Retell PreK
   8          |          1        |     3    |     20     | Recount / Narrative Retell Kindergarten
   9          |          1        |     4    |     20     | Recount / Narrative Retell Grade 1
   10         |          1        |     5    |     20     | Recount Grade 2
   11         |          1        |     6    |     20     | Recount Grade 3

competency_set_id是competency_set.competency_set_id的外键（PK）。
grade_id是grade.grade_id的外键（PK）。
理想情况下，我希望得到一个结果，允许我以非常简单的方式循环，即：

$rows = $this->getTableRows(); // the method that will return the mysql query result
foreach ($rows as $row) {
    echo '<tr>';
    echo '<td>' . $row->competencySetTitle . '</td>';
    echo '<td>' . $row->preschool . '</td>';
    echo '<td>' . $row->prek . '</td>';
    echo '<td>' . $row->kindergarten . '</td>';
    echo '<td>' . $row->grade1 . '</td>';
    echo '<td>' . $row->grade2 . '</td>';
    echo '<td>' . $row->grade3 . '</td>';
    echo '</tr>';
}

以下是模型的截图，其中解释了不同的部分（能力、能力集和等级）：

最佳答案

也许这不完全是你想要的解决方案，但也许是一个起点：

select competency_set_id,
 group_concat(CASE grade_id WHEN 1 THEN title END ORDER BY competency_id SEPARATOR '<hr>') presch,
 group_concat(CASE grade_id WHEN 2 THEN title END ORDER BY competency_id SEPARATOR '<hr>') preK,
 group_concat(CASE grade_id WHEN 3 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Kinderg,
 group_concat(CASE grade_id WHEN 4 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade1,
 group_concat(CASE grade_id WHEN 5 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade2,
 group_concat(CASE grade_id WHEN 6 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade3
 FROM comp GROUP BY competency_set_id

看看这里的小live Demo。
我把注意力集中在最后一张表上，因为它包含了所有重要的数据。核心问题是，对于每个能力集和级别，您可能具有多个能力。在建议的解决方案中，您有一个“常规”表布局，每个补偿集有多行。这是我用简单的查询无法实现的。相反，我用<hr>s（仅用于演示目的）来分隔每个能力/等级单元中的能力。
正如供思考的食物：在你的解决方案表中，每个能力组中的各个线是否真正正确地连接起来，它们指的是它们有共同点的东西？如果没有，那么也许我更简单的方法还是有意义的？
MySQL中的完整解决方案：
另一方面，如果您的每一个表格行都引用了跨越所有年级的某种能力，那么您可能也应该在competency表格中以新的int列competency_type的形式提供此信息。然后，此列应指向（另一个）可以在单独表中设置的能力类型表。但大多数附加列将使您能够通过简单的sql查询轻松实现所需的结果表布局，有关updated demo

select competency_set_id ci,competency_type ct,
 group_concat(CASE grade_id WHEN 1 THEN title END ORDER BY competency_id SEPARATOR '<hr>') presch,
 group_concat(CASE grade_id WHEN 2 THEN title END ORDER BY competency_id SEPARATOR '<hr>') preK,
 group_concat(CASE grade_id WHEN 3 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Kinderg,
 group_concat(CASE grade_id WHEN 4 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade1,
 group_concat(CASE grade_id WHEN 5 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade2,
 group_concat(CASE grade_id WHEN 6 THEN title END ORDER BY competency_id SEPARATOR '<hr>') Grade3
 FROM comp GROUP BY competency_set_id,competency_type
 ORDER BY competency_set_id,competency_type

我保留了在查询中每个单元格有多个能力的可能性，但是一旦您将所有能力分配给每个年级的唯一competency_types，您就可以安全地用更简单的group_concat函数替换max()s。