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

发布于 2024-10-21 02:23:59 字数 663 浏览 7 评论 0原文

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

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

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

我从结果中需要的数据结构如下：

-group 2
      - field 3
      - field 2

- group 1
      - field 1

是否可以从一个 mysql 查询中获取这些结果？我已经阅读了有关分层数据的 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:

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.

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.

Thanks for looking

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

你的呼吸 2024-10-28 02:23:59

您不需要考虑分层数据，您应该能够选择字段并加入组信息。尝试类似的操作：

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

然后您将获得每个字段作为具有适用组的行，并且也按照正确的组顺序。您的循环应该能够处理您需要的显示。

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.

回复收藏 0 原文