获取父 ID 的所有嵌套子项 [英] get all nested children for a parent id

问题描述

开始摆弄

Work Table
ProductId, LabelName, CategoryId, ChildCategoryId
------------------------------------
1, Widget A, 1, null
null, Category A, 2, 1 
2, Widget B, 3, null

Categories Table
CategoryId, CategoryName
---------------------------
1, Category A
2, Category B
3, Category C

根据上述信息，您将如何获得产品 ID 的所有类别?

Given the information above, how would you get all categories for a product id?

例如，给定产品 ID 为 1，以下将是所需的结果.

For example, given a product id of 1, the following would be the desired results.

Desired Results
ProductId, LabelName, CategoryId, ChildCategoryId
------------------------------------
1, Widget A, 1, null
null, Category A, 2, 1 
null, Category B, null, 2

它应该是分层数据，我很抱歉无法很好地解释.这简直令人难以置信.小部件 A 的产品 id 为 1，类别 id 为 1.这意味着包含 ChildCategoryId 为 1 的所有记录，这为我们提供了类别 A.CatA 的类别 id 为 2，因此像以前一样，所有具有结果中包含 2 的 ChildCategoryId，这就是包含类别 B 的原因.

It is supposed to be hierarchical data and I apologize for not being able to explain very well. It is just boggling my mind. Widget A has a product id of 1 and category id of 1. This means all records that have a ChildCategoryId of 1 is included, which gives us Category A. CatA has a category id of 2, so like before, all records that have a ChildCategoryId of 2 is included in result, which is why Category B is included.

推荐答案

这个乱七八糟的从样本数据产生样本结果.仍然不清楚你认为算法应该是什么.

This mess produces the sample result from the sample data. It still isn't clear what you think the algorithm ought to be.

declare @CategoryItems as Table (
  CategoryName NVarChar(255),
  Label NVarChar(255),
  ProductId Int,
  ChildCategoryId Int,
  CategoryId Int );

declare @Categories as Table (
  CategoryId Int,
  Name NVarChar(100) );

insert into @CategoryItems ( CategoryName, Label, ProductId, ChildCategoryId, CategoryId ) values
  ( 'CategoryA', 'Widget A', 1, 0, 1 ),
  ( 'CategoryB', 'CategoryA', 0, 1, 2 ),
  ( 'CategoryC', 'Widget B', 2, 0, 3 );
insert into @Categories ( CategoryId, Name ) values
  ( 1, 'CategoryA' ),
  ( 2, 'CategoryB' ),
  ( 3, 'CategoryC' );

select * from @Categories;
select * from @CategoryItems;

declare @TargetProductId as Int = 1;

with Leonard as (
  -- Start with the target product.
  select 1 as [Row], ProductId, Label, CategoryId, ChildCategoryId
    from @CategoryItems
    where ProductId = @TargetProductId
  union all
  -- Add each level of child category.
  select L.Row + 1, NULL, CI.Label, CI.CategoryId, CI.ChildCategoryId
    from @CategoryItems as CI inner join
      Leonard as L on L.CategoryId = CI.ChildCategoryId ),
  Gertrude as (
    -- Take everything that makes sense.
    select Row, ProductId, Label, CategoryId, ChildCategoryId
      from Leonard
    union
    -- Then tack on an extra row for good measure.
    select L.Row + 1, NULL, C.Name, NULL, C.CategoryId
      from Leonard as L inner join
        @Categories as C on C.CategoryId = L.CategoryId
      where L.Row = ( select Max( Row ) from Leonard ) )
  select Row, ProductId, Label, CategoryId, ChildCategoryId
    from Gertrude
    order by Row;

我怀疑问题在于您以不平衡的方式混合了数据.类别的层次结构通常表示为:

I suspect that the problem is that you have mixed your data in a lopsided manner. A hierarchy of categories is usally represented something like:

declare @Categories as Table (
  CategoryId Int Identity,
  Category NVarChar(128),
  ParentCategoryId Int Null );

每个层次结构的根由ParentCategoryId is NULL表示.这允许任意数量的独立树共存于一个表中，并且不依赖于任何产品的存在.

The root of each hierarchy is indicated by ParentCategoryId is NULL. This allows any number of independent trees to coexist in a single table and does not depend on the existence of any products.

如果产品被分配到单个(子)类别，则只需在 Products 表中包含 CategoryId.如果一个产品可能被分配到几个(子)类别，可能在不同的层次结构中，那么使用单独的表格来关联它们:

If products are assigned to a single (sub)category then just include the CategoryId in the Products table. If a product may be assigned to several (sub)categories, possibly in different hierarchies, then use a separate table to relate them:

declare @ProductCategories as Table (
  ProductId Int,
  CategoryId Int );

这篇关于获取父 ID 的所有嵌套子项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！