在表中以层次结构方式添加产品 [英] Add product in hierarchy manner in a table
问题描述
大家好，
我有一个表，其中一列的类型为hierarchyid。现在我想为该新产品添加一个产品和子产品。你能帮忙知道如何获得现有产品的最后一个id并添加子产品。
该列的结构如/ 1 /，/ 1 / 1 /，/ 1/1/1 /等等。这样我们就有了/ 7 /，/ 7/1 / .....现在我想添加一个像/ 8 /，/ 8/1 /，/ 8/1/1这样的新版本。
我尝试了什么：
我使用以下查询添加了新记录。但该产品正在UI的第一行添加。它应该在最后一行添加。 TaxonomyNode是一个hirarchyid列。 taxonomyid是标识列。
插入到tblname
（TaxonomyId， TaxonomyNode ，TaxonomyName，DeletedInd，CreatedOn， CreatedBy，UpdatedOn，UpdatedBy）
values（'/ 8 /'，'test'，'0'，getdate（），'abd'，getdate（）， 'abd'）
Hi All,
I have a table whose one columns is of type hierarchyid. Now I want to add one more product and sub products to that new product. Could you please help to how to get the last id of existing product and add child product to it.
Structure of that column is like /1/,/1/1/,/1/1/1/ and so on. In this way we have /7/,/7/1/..... Now I want to add new one like /8/, /8/1/,/8/1/1 and so on.
What I have tried:
I have added the new record using below query. But the product is getting added on first row on UI. It should be get added on last row. TaxonomyNode is a hirarchyid column. taxonomyid is identity column.
insert into tblname
(TaxonomyId,TaxonomyNode,TaxonomyName,DeletedInd,CreatedOn,CreatedBy,UpdatedOn,UpdatedBy)
values ('/8/','test','0',getdate(),'abd',getdate(),'abd')
推荐答案
在表格中创建层次关系有两种基本方法。
1）单个表，父条目以及每个条目（子条目）都有一列将其与父条目相关联（父母的子项应该是其本身）。这可以继续， ad nauseam 创建任何深度的树。这是一种愚蠢而低效的设计 - 但它可以起作用。在某种情况下，这是解决方案，但是，它更好。 。 。
2）创建第二个表 - 有一个列引用第一个（父）表的ID，具有相同引用的所有内容都是同一个父项的子项。您可以根据自己的意愿挂出父母一样多的桌子，甚至可以将孩子挂在孩子身上。它灵活，能够进行许多类型的更改，对现有代码没有任何影响。
由于SQL，MySQL和& etc是参考数据库，使用（2），参考方法，最好是照顾你的问题。请注意，您可以将现有表用作父表，创建子表，并且除非您让他们知道，否则使用原始父表的所有内容都不会知道它。
There are two basic ways to create a hierarchical relationship in your tables.
1) A single table, a parent entry and each further (child entry) has a column to associate it to the parent entry (the parent's child should be itself). This could go on, ad nauseamcreating a tree of any depth. This is a dumb and inefficient design - but it can be made to work. There are situations where this is "the" solution, but, it is better to . . .
2) Create a second table - have a column referencing the ID of the first (parent) table and everything with the same reference are children of the same parent. You can hang as many table off of the parent as you wish, and even hang children on the children. It's flexible and capable of a great many types of changes that have no effect on existing code.
Since SQL, MySQL, &etc are "Referential" databases, using (2), a referential methodology, would be best at taking care of your problems. Note that you can use the existing table as parent, create your child table, and everything that used the original parent won't even be aware of it unless you make them aware.
我使用下面的查询添加了新记录。 但该产品正在UI上的第一行添加。它应该被添加到最后一行 。 TaxonomyNode是一个hirarchyid列。 taxonomyid是标识栏。
I have added the new record using below query. But the product is getting added on first row on UI. It should be get added on last row. TaxonomyNode is a hirarchyid column. taxonomyid is identity column.
没有这样的表达：第一行或最后一行 关系数据库的术语[ ^ ]。记录顺序取决于许多因素，例如：索引，主键和外键等.UI中的数据顺序取决于应用程序的设置。
如果你想要按特定顺序对数据进行排序，尤其是当您想要显示它们时，您必须使用 ORDER BY子句 [ ^ ]。
如需了解更多详情，请阅读本文：
分层数据（SQL Server） Microsoft Docs [ ^ ]
分级数据（SQL Server） [ ^ ]
教程：使用hierarchyid数据类型| Microsoft Docs [ ^ ]
以及：
sql - 数据库索引如何工作？ - 堆栈溢出 [ ^ ]
There's no such of expression: "first row" or "last row" in a terminology of relational database[^]. An order of records depends on many factors, for example: index, primary and foreign keys, etc. An order of data in UI depends of application's settings.
If you want to sort data in a specific order, especially when you want to display them, you have to use ORDER BY clause[^].
For further details, please read this:
Hierarchical Data (SQL Server) | Microsoft Docs[^]
Hierarchical Data (SQL Server)[^]
Tutorial: Using the hierarchyid Data Type | Microsoft Docs[^]
and also:
sql - How does database indexing work? - Stack Overflow[^]
我找到了解决方案。我们不需要使用GetAncestor等功能。我们可以直接在hierarchyid列中插入记录，例如
Hi,
I have found the solution. We do not need to use such a functions like GetAncestor or others. We can insert the record directly in hierarchyid columns like
insert into tblname values ('/5/23/1/','prod name','0',getdate(),'abd',getdate(),'abd')
我已经实现了这种方式及其工作。
I have implemented this way and its working.
这篇关于在表中以层次结构方式添加产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
