需要一些SQL查询（SQL Server 2005）来帮助转换这些数据

时间：2022-08-31 01:56:56

Here's a simplistic look at the table I have

这是我对表格的简单看法

Category

  • CategoryId
  • CategoryName
  • ParentCategoryId

As you can see, the catalog table is a self referencing table, so recursing it is possible by starting at 0, which is the fake "root" category (could just as well be null, but it's not).

正如您所看到的,目录表是一个自引用表,因此可以从0开始递归它,这是假的“根”类别(也可以是null,但事实并非如此)。

Sample Data:

1, Apples, 0
5, Yummy, 1
10, Really Yummy, 5
15, Yucky, 0
18, Some Sub Cat, 15
20, Some Deep Sub Cat, 18
25, Some Deep Sub Cat 2, 18

The deepest any category hierarchy can be is 4 deep, and I'm trying to get an output that looks like this:

最深的任何类别层次结构都可以是4深,我正在尝试获得如下所示的输出:

CatId, [up to 4 deep of category names on the hierarchy in separate columns, or null if none]
1, Apples, NULL, NULL, NULL
5, Apples, Yummy, NULL, NULL
10, Apples, Yummy, Really Yummy, NULL
15, Apples, Yucky, NULL, NULL
18, Apples, Yucky, Some Sub Cat, NULL
20, Apples, Yucky, Some Sub Cat, Some Deep Sub Cat
25, Apples, Yucky, Some Sub Cat, Some Deep Sub Cat 2

This SQL is close, but it generates it backwards, left justified

这个SQL很接近,但是它向后生成它,左对齐

select c1.categoryid, c1.name, c2.name, c3.name, c4.name
from category c1
    left outer join category c2
        on c1.parentcategoryid = c2.categoryid
    left outer join category c3
        on c2.parentcategoryid = c3.categoryid
    left outer join category c4
        on c3.parentcategoryid = c4.categoryid

Any SQL geniuses have some good ideas?

任何SQL天才都有一些好主意吗?

1 个解决方案

#1

select c1.CategoryId as id,
       c1.CategoryName as n1,
       c2.CategoryName as n2,
       c3.CategoryName as n3,
       c4.CategoryName as n4
from            Category c1
left outer join Category c2 on c2.ParentCategoryId = c1.CategoryId
left outer join Category c3 on c3.ParentCategoryId = c2.CategoryId
left outer join Category c4 on c4.ParentCategoryId = c3.CategoryId
where c1.parentcategoryid = 0;

(per your edit: you've just got ParentCategoryId and CategoryID flipped in the joins.)

(根据你的编辑:你刚刚在连接中翻转了ParentCategoryId和CategoryID。)

#1

select c1.CategoryId as id,
       c1.CategoryName as n1,
       c2.CategoryName as n2,
       c3.CategoryName as n3,
       c4.CategoryName as n4
from            Category c1
left outer join Category c2 on c2.ParentCategoryId = c1.CategoryId
left outer join Category c3 on c3.ParentCategoryId = c2.CategoryId
left outer join Category c4 on c4.ParentCategoryId = c3.CategoryId
where c1.parentcategoryid = 0;

(per your edit: you've just got ParentCategoryId and CategoryID flipped in the joins.)

(根据你的编辑:你刚刚在连接中翻转了ParentCategoryId和CategoryID。)

标签：

相关文章