我有一个部分列表,我们想链接到一个孩子/父母的关系,没有层次的限制.
当我从零件清单中挑选一个孩子给父母时,我限制零件清单,以排除父母本身以及已经是父母子女的任何部分.
我发现的是,我也想排除父母的祖父母,否则我们可以得到一个乱伦的关系,当我显示部分的树将创建一个无限循环.
不仅如此,但我不能允许小孩部分成为父母或伟大的祖父母e祖母.
这里是我目前使用的sql语句,我认为也可以通过使用LEFT JOIN来改进,但是我在这一点上还不够熟练使用sql.
SELECT * FROM sch_part_general WHERE (sch_part_general.part_id <> $parentId) AND (sch_part_general.part_id NOT IN (SELECT part_id FROM sch_part_mapping WHERE parent_id = $parentId) )
sch_part_general是一个包含所有部分的多列表,part_id为主键.
sch_part_mapping是一个带有part_id(child)||的两列映射表parent_id(parent).
有人可以用SQL查询指向正确的方向吗？我不喜欢使用while循环创建sql语句,因为我认为这将是非常低效的,但它是我认为的唯一方法可能工作到目前为止.
另一方面,如果您可以更改数据库架构,我建议实现嵌套集模型.
嵌套集模型的非常好的解释在Mike Hillyer’s blog提出
Limitations of the Adjacency List Model
Working with the adjacency list model in pure sql can be difficult at
best. Before being able to see the full path of a category we have to
know the level at which it resides.Nested Set Model
the concept of nested sets in sql has been around for over a decade,and there is a lot of additional information available in books and on the Internet. In my opinion the most comprehensive source of information on managing hierarchical information is a book called Joe Celko’s Trees and Hierarchies in sql for Smarties,written by a very respected author in the field of advanced sql,Joe Celko.
