如何使用分层数据创建mysql连接查询

I need to create a join query for hierarchical data across 2 tables. These tables can have unlimited amounts of data and their structures are as follows:

我需要为2个表中的分层数据创建连接查询。这些表可以包含无限量的数据，其结构如下：

group_id      group_name       group_order
   1            group 1            2
   2            group 2            1


field_id    field_name    parent_group    field_order
   1         field 1           1               1
   2         field 2           2               2
   3         field 3           2               1

I am currently able to get the correct format of data using 2 select queries with the second query inside a loop created from the results of the first query on the groups table.

我目前能够使用2个选择查询获得正确的数据格式，第二个查询位于根据groups表上第一个查询的结果创建的循环内。

The structure of the data I require from the result is as follows:

我从结果中得到的数据结构如下：

-group 2
      - field 3
      - field 2

- group 1
      - field 1

Is it possible to get these results from one mysql query? I have read through the mysql document on hierarchical data by I am confused about how to incorporate the join.

是否有可能从一个mysql查询中获取这些结果？我已经阅读了关于分层数据的mysql文档，我对如何合并连接感到困惑。

Thanks for looking

谢谢你的期待

2 个解决方案

#1

You shouldn't need to think about it in terms of hierarchical data, you should just be able to select your fields and join on your group information. Try something like:

您不应该在分层数据方面考虑它，您应该只能选择您的字段并加入您的组信息。尝试以下方法：

SELECT * 
FROM Fields AS F
INNER JOIN Groups AS G 
ON G.group_id = F.parent_group 
ORDER BY group_order, field_order

Then you will get each fields as a row with the applicable group, also in the correct group order. Your loop should be able to handle the display you need.

然后，您将使用适用的组将每个字段作为一行，也以正确的组顺序。你的循环应该能够处理你需要的显示。

#2

one method

一种方法

something that may convince you change your db schema

可能会说服您更改数据库架构的内容

#1