我有一个部分列表,我们想链接到一个孩子/父母的关系,没有层次的限制.
当我从零件清单中挑选一个孩子给父母时,我限制零件清单,以排除父母本身以及已经是父母子女的任何部分.
我发现的是,我也想排除父母的祖父母,否则我们可以得到一个乱伦的关系,当我显示部分的树将创建一个无限循环.
不仅如此,但我不能允许小孩部分成为父母或伟大的祖父母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语句,因为我认为这将是非常低效的,但它是我认为的唯一方法可能工作到目前为止.
另一方面,如果您可以更改数据库架构,我建议实现嵌套集模型.
嵌套集模型的非常好的解释在@L_502_2@提出
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.
