mysql - 如何检索多个表关联的分层父子数据？

Question

我有一个网站，其中包含按类别分割的吉他类(class)和练习。所以你可以有类别scales。然后，类(class)扩展了 Lesson1，其中可能包含 exercise1_1、exercise1_2。对于其他类别和带有练习的类(class)也是如此。

类(class)和练习被视为节点(它是一个 Drupal 站点)。因此有一个节点表，其中包含节点 ID、节点类型(类(class)或练习)和标题。

这些节点的其他信息字段(类(class)/练习文本等)存储在每个字段的单独表中。例如，有一个 drupal_field_data_description 表，其中包含每节课和练习的说明。

类别存储在分类term 表中。

类别之间的关系是通过建立子父关系的分类索引表来处理的(因此您可以有比例尺，比例尺->主要比例尺等)。 对于我的问题，我只是考虑一种类别的深度。

类(class)和练习的类别存储在表 drupal_field_data_field_category 中，该表将类(class)和练习映射到它们所属的类别。

练习-类(class)子父关系存储在将练习映射到类(class)的表 drupal_field_data_field_lesson 中。

这里是示例数据:

类别(drupal_taxonomy_term_data):

tid vid name
1   2   Scales
2   2   Arpeggios

类(class)和练习(drupal_node):

nid type        title
1   lesson      Lesson1
2   lesson      Lesson2
3   exercise    Ex1_1
4   exercise    Ex1_2
5   exercise    Ex2_1
6   exercise    Ex2_2

类(class)和练习的描述字段 (drupal_field_data_field_description):

entity_type     bundle   entity_id  field_description_value
node            lesson   1          Lesson1Summary
node            lesson   2          Lesson2Summary
node            exercise 3          Ex1_1Summary
node            exercise 4          Ex1_2Summary
node            exercise 5          Ex2_1Summary
node            exercise 6          Ex2_2Summary

类(class)和练习与分类的映射(drupal_taxonomy_index):

nid tid
1   1
2   1
3   1
4   1
5   1
6   1

类(class)和练习到类别(drupal_field_data_field_category)的映射(由于分类索引的原因，这几乎似乎是不必要的):

entity_type bundle   entity_id  field_category_tid
node        lesson   1          1
node        lesson   2          1
node        exercise 3          1
node        exercise 4          1
node        lesson   5          1
node        lesson   6          1

练习到类(class)的映射(drupal_field_data_field_lesson):

entity_type     bundle      entity_id   field_lesson_target_id
node            exercise    3           1
node            exercise    4           1
node            exercise    5           2
node            exercise    6           2

所以...使用这种结构，我无法弄清楚如何构建将返回表单结果的查询

Lesson1 Lesson1Summary
Ex1_1 Ex1_1Summary
Ex1_2 Ex1_2Summary
Lesson2 Lesson2Summary
Ex2_1 Ex2_1Summary
Ex2_2 Ex2_2Summary

请注意，Lesson1 和 Lesson2 属于同一类别。

我需要返回此类数据，因为对于类别页面(没有子类别)，我需要为每节课显示一个表格，其中显示类(class)中的练习。

我可以在多个查询中完成所有这些操作，但我真的想更好地理解 SQL 连接和分组。另外，我并不执着于如上所示的结果集。我对任何能够让我以我所描述的方式轻松显示数据(我将通过 PHP 执行)的结果集持开放态度。

SQL fiddle is here

您建议如何构建这样的查询来提取类(class)及其以逻辑方式分组的练习(例如我上面显示的方式)？

似乎以这种方式获得类(class)和练习相当于自联接，其他表上有各种内部联接，但我无法将它们全部拼凑在一起......

Answer 1

好吧，经过大量阅读，我想我明白了:

SELECT n.title, d.field_description_value, n.nid, l.field_lesson_target_id from drupal_node n

JOIN drupal_field_data_field_description AS d ON d.entity_id = n.nid

JOIN drupal_taxonomy_index AS t ON t.nid = n.nid

LEFT JOIN drupal_field_data_field_lesson AS l ON l.entity_id = n.nid

ORDER BY COALESCE(l.field_lesson_target_id, n.nid), l.field_lesson_target_id, n.nid

我基于以上this post

我的sqlfiddle is here .

这对我来说绝对是一个新领域，虽然上面的方法可行，但我希望我能理解 ORDER BY 和 GROUP BY 的细微差别，以了解在哪里/如何使用它们。