当前位置： 文江博客话题详情

SQL：使用公共表表达式递归获取父记录

发布于 2024-08-28 15:15:46 字数 1080 浏览 10 评论 0原文

假设您必须遵循以下表格，其中销售由产品组成，并且产品可以放置在多个类别中。其中类别具有如下层次结构：

Man
 Shoes
  Sport
  Casual
 Watches
Women
 Shoes
  Sport
  Casual
 Watches

表格：

Sale:
    id name 
    1  Sale1

Product:
    id saleidfk name 
    1  1        a
    2  1        b
    3  1        c
    4  1        d
    5  1        e

ProductCategory :
    productid categoryid 
    1         3
    2         3           
    3         4
    4         5
    5         10     

Category:
    id ParentCategoryIdFk name 
    1  null               Men
    2  1                  Shoes
    3  2                  Sport
    4  2                  Casual
    5  1                  Watches
    6  null               Women
    7  6                  Shoes
    8  7                  Sport
    9  7                  Casual
    10 6                 Watches

问题：

现在在我的网站上，我想创建一个控件，其中仅显示特定销售的类别，并且类别中填充了销售的产品。我也想要 包括类别的层次结构。因此，如果我们有一个叶节点，请递归地到达顶部节点。

因此，对于 sale1，我应该有一个具有以下结果的查询：

Men
  Shoes
    Sport
    Casual
  Watches
Women
  Watches
原文

Suppose you have to following tables where a sale consists of products and a product can be placed in multiple categories. Whereby categories have a hierarchy structure like:

Man
 Shoes
  Sport
  Casual
 Watches
Women
 Shoes
  Sport
  Casual
 Watches

Tables:

Sale:
    id name 
    1  Sale1

Product:
    id saleidfk name 
    1  1        a
    2  1        b
    3  1        c
    4  1        d
    5  1        e

ProductCategory :
    productid categoryid 
    1         3
    2         3           
    3         4
    4         5
    5         10     

Category:
    id ParentCategoryIdFk name 
    1  null               Men
    2  1                  Shoes
    3  2                  Sport
    4  2                  Casual
    5  1                  Watches
    6  null               Women
    7  6                  Shoes
    8  7                  Sport
    9  7                  Casual
    10 6                 Watches

Question:

Now on my website I want to create a control where only the categories are shown of a certain sale and where the categories are filled with the products of the sale. I also want
to include the hierarchy structure of the categories. So if we have a leaf node, recursively go up to the top node.

So with sale1 I should have a query with the following result:

Men
  Shoes
    Sport
    Casual
  Watches
Women
  Watches

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（5）

温柔一刀 2024-09-04 15:15:46

尝试这样的操作 - 获取类别的分层列表的基本 CTE 与此类似：

WITH Categories AS
(
    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, CAST('none' AS VARCHAR(50)) AS 'ParentCategory', 1 AS 'Level'
    FROM dbo.MBCategory Cat
    WHERE Cat.ParentCategoryID IS NULL

    UNION ALL

    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, c2.NAME AS 'ParentCategory', LEVEL + 1
    FROM dbo.MBCategory CAT
    INNER JOIN Categories c2 ON cat.ParentCategoryID = c2.ID
)
SELECT * FROM Categories

现在您需要做的是将其他表连接到此 CT​​E，最终获得以下查询：

WITH Categories AS
(
    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, CAST('none' AS VARCHAR(50)) AS 'ParentCategory', 1 AS 'Level'
    FROM dbo.MBCategory Cat
    WHERE Cat.ParentCategoryID IS NULL

    UNION ALL

    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, c2.NAME AS 'ParentCategory', LEVEL + 1
    FROM dbo.MBCategory CAT
    INNER JOIN Categories c2 ON cat.ParentCategoryID = c2.ID
)
SELECT DISTINCT s.*, c.*
FROM dbo.Sale s
INNER JOIN dbo.Product p ON p.SaleID = s.ID
INNER JOIN dbo.ProductCategory pc ON p.ID = pc.ProductID
INNER JOIN Categories c ON pc.CategoryID = c.ID
ORDER BY Level

这给了我一个结果输出类似：

ID  Name   CatID  CatName  ParentCatID  ParentCatName Level
 1  Sale1    5    Watches      1            Men         2
 1  Sale1   10    Watches      6            Women       2 
 1  Sale1    3    Sport        2            Shoes       3
 1  Sale1    3    Sport        2            Shoes       3
 1  Sale1    4    Casual       2            Shoes       3

Try something like this - the basic CTE to get a hierarchical listing of your categories would be similar to this:

WITH Categories AS
(
    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, CAST('none' AS VARCHAR(50)) AS 'ParentCategory', 1 AS 'Level'
    FROM dbo.MBCategory Cat
    WHERE Cat.ParentCategoryID IS NULL

    UNION ALL

    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, c2.NAME AS 'ParentCategory', LEVEL + 1
    FROM dbo.MBCategory CAT
    INNER JOIN Categories c2 ON cat.ParentCategoryID = c2.ID
)
SELECT * FROM Categories

Now what you need to do is join your other tables to this CTE, to get the following query in the end:

WITH Categories AS
(
    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, CAST('none' AS VARCHAR(50)) AS 'ParentCategory', 1 AS 'Level'
    FROM dbo.MBCategory Cat
    WHERE Cat.ParentCategoryID IS NULL

    UNION ALL

    SELECT Cat.ID, Cat.NAME, Cat.ParentCategoryID, c2.NAME AS 'ParentCategory', LEVEL + 1
    FROM dbo.MBCategory CAT
    INNER JOIN Categories c2 ON cat.ParentCategoryID = c2.ID
)
SELECT DISTINCT s.*, c.*
FROM dbo.Sale s
INNER JOIN dbo.Product p ON p.SaleID = s.ID
INNER JOIN dbo.ProductCategory pc ON p.ID = pc.ProductID
INNER JOIN Categories c ON pc.CategoryID = c.ID
ORDER BY Level

This gives me a resulting output something like:

ID  Name   CatID  CatName  ParentCatID  ParentCatName Level
 1  Sale1    5    Watches      1            Men         2
 1  Sale1   10    Watches      6            Women       2 
 1  Sale1    3    Sport        2            Shoes       3
 1  Sale1    3    Sport        2            Shoes       3
 1  Sale1    4    Casual       2            Shoes       3
回复 原文
放飞的风筝 2024-09-04 15:15:46

我认为，如果您创建一个额外的表来列出每个类别的所有祖先类别（父母、祖父母等），您将获得最快的性能和更清晰的 SQL 查询，如下所示：

CategoryAncestor
ID   categoryid   ancestorid    
1         1            1      -- Men, obligatory self reference (makes queries easier)
2         2            2      -- Shoes, self reference 
3         2            1      -- Shoes is a subcategory of Men
4         3            3      -- Sport, self reference 
5         3            2      -- Sport is a subcategory of Shoes
6         3            1      -- Sport is ALSO a subcategory of Men
-- etc.

这将创建更多的 SQL插入新类别或删除它们时的开销，但可以让您更快地运行查询。

您可能需要考虑做的下一件事是将排名和级别列添加到类别（同样，创建和删除类别时需要做更多工作）：

id ParentCategoryIdFk name       level    rank
1  null               Men           0       1
2  1                  Shoes         1       2
3  2                  Sport         2       3
4  2                  Casual        2       4
5  1                  Watches       1       5
6  null               Women         0       6
7  6                  Shoes         1       7
8  7                  Sport         2       8
9  7                  Casual        2       9
10 6                 Watches        1      10

排名列指定排序顺序。

然后，您可以简单地运行以下查询：

SELECT * FROM Category c
  WHERE c.id IN (
    SELECT ancestorid FROM CategoryAncestor ca, ProductCategory pc, Product p
      WHERE p.id = pc.productid 
        AND pc.categoryid = ca.categoryid
        AND p.saleidfk = 1
    )
  ORDER BY rank

希望这有帮助。

I think you'll get the fastest performance and also much cleaner SQL queries if you create an additional table that lists all the ancestor categories (parent, grandparent, etc.) for every category, something like this:

CategoryAncestor
ID   categoryid   ancestorid    
1         1            1      -- Men, obligatory self reference (makes queries easier)
2         2            2      -- Shoes, self reference 
3         2            1      -- Shoes is a subcategory of Men
4         3            3      -- Sport, self reference 
5         3            2      -- Sport is a subcategory of Shoes
6         3            1      -- Sport is ALSO a subcategory of Men
-- etc.

This will create a little more SQL overhead when you insert new categories or delete them, but will allow you to run you query much faster.

The next thing you may want to consider doing is adding rank and level columns to categories (again, more work when creating and deleting categories):

id ParentCategoryIdFk name       level    rank
1  null               Men           0       1
2  1                  Shoes         1       2
3  2                  Sport         2       3
4  2                  Casual        2       4
5  1                  Watches       1       5
6  null               Women         0       6
7  6                  Shoes         1       7
8  7                  Sport         2       8
9  7                  Casual        2       9
10 6                 Watches        1      10

The rank column specifies the sort order.

Then, you can simply run the following query:

SELECT * FROM Category c
  WHERE c.id IN (
    SELECT ancestorid FROM CategoryAncestor ca, ProductCategory pc, Product p
      WHERE p.id = pc.productid 
        AND pc.categoryid = ca.categoryid
        AND p.saleidfk = 1
    )
  ORDER BY rank

Hope this helps.

回复 原文
云胡 2024-09-04 15:15:46

它不是特别有效，但是如果您想要做的是有效地“分解”整个层次结构并按顺序从父级到叶级获得结果，类似这样的事情就可以做到：

WITH CategoryHierarchy AS
(
    SELECT
        ID, ParentCategoryIdFk, 0 AS Level,
        ROW_NUMBER() OVER (ORDER BY ID) AS SubTreeID
    FROM Category
    WHERE CategoryID IN
    (
        SELECT pc.CategoryID
        FROM Sale s
        INNER JOIN Product p
            ON p.saleidfk = s.id
        INNER JOIN ProductCategory pc
            ON pc.productid = p.id
        WHERE s.id = @SaleID
    )

    UNION ALL

    SELECT c.ID, c.ParentCategoryIdFk, h.Level + 1, h.SubTreeID
    FROM CategoryHierarchy h
    INNER JOIN Category c
        ON c.ID = h.ParentID
)
SELECT c.ID, c.ParentCategoryIdFk AS ParentID, c.Name
FROM CategoryHierarchy h
INNER JOIN Category c
    ON c.ID = h.ID
ORDER BY h.SubTreeID ASC, h.Level DESC

这应该会得到类似于以下内容的结果：

ID | ParentID | Name
---+----------+----------
1  |     NULL | Men
2  |        1 | Shoes
3  |        2 | Sport
---+----------+----------
1  |     NULL | Men
2  |        1 | Shoes
4  |        2 | Casual
---+----------+----------
1  |     NULL | Men
5  |        1 | Watches
---+----------+----------
6  |     NULL | Women
10 |        6 | Watches

当然实际结果不会有这样的分隔符，我添加了这些分隔符以使结果的含义更清晰。

如果您不希望它像这样完全爆炸，您可以使用另一个 rownum 只返回每个父级的第一个实例：

WITH CategoryHierarchy AS
(
    SELECT
        ID, ParentCategoryIdFk, 0 AS Level,
        ROW_NUMBER() OVER (ORDER BY ID) AS SubTreeID
    FROM Category
    WHERE CategoryID IN
    (
        SELECT pc.CategoryID
        FROM Sale s
        INNER JOIN Product p
            ON p.saleidfk = s.id
        INNER JOIN ProductCategory pc
            ON pc.productid = p.id
        WHERE s.id = @SaleID
    )

    UNION ALL

    SELECT c.ID, c.ParentCategoryIdFk, h.Level + 1, h.SubTreeID
    FROM CategoryHierarchy h
    INNER JOIN Category c
        ON c.ID = h.ParentID
),
Filter_CTE AS
(
    SELECT
        ID, Level, SubTreeID
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SubTreeID) AS RowNum
    FROM CategoryHierarchy
)
SELECT c.ID, c.ParentCategoryIdFk AS ParentID, c.Name
FROM Filter_CTE f
INNER JOIN Category c
    ON c.ID = f.ID
WHERE f.RowNum = 1
ORDER BY f.SubTreeID ASC, f.Level DESC

...将为您提供类似于以下结果：

ID | ParentID | Name
---+----------+----------
1  |     NULL | Men
2  |        1 | Shoes
3  |        2 | Sport
4  |        2 | Casual
5  |        1 | Watches
6  |     NULL | Women
10 |        6 | Watches

注意： 请小心第二个版本，因为它不一定保证按层次结构顺序返回结果。碰巧这个版本确实如此，因为 ID 本身是按层次顺序排列的。您可以绕过此限制，但它会给这个已经有些复杂的查询增加很多复杂性。

第二个版本确实保证主类别始终出现在其任何子类别之前，如果您计划使用字典构建递归数据结构，这很好。它可能不适合更快的基于堆栈的树构建或直接向用户报告。出于这些目的，您可能需要使用第一个版本。

It's not particularly efficient, but if what you want to do is effectively "explode" the entire hierarchy and get the results in sequence from parent to leaf, something like this would do it:

WITH CategoryHierarchy AS
(
    SELECT
        ID, ParentCategoryIdFk, 0 AS Level,
        ROW_NUMBER() OVER (ORDER BY ID) AS SubTreeID
    FROM Category
    WHERE CategoryID IN
    (
        SELECT pc.CategoryID
        FROM Sale s
        INNER JOIN Product p
            ON p.saleidfk = s.id
        INNER JOIN ProductCategory pc
            ON pc.productid = p.id
        WHERE s.id = @SaleID
    )

    UNION ALL

    SELECT c.ID, c.ParentCategoryIdFk, h.Level + 1, h.SubTreeID
    FROM CategoryHierarchy h
    INNER JOIN Category c
        ON c.ID = h.ParentID
)
SELECT c.ID, c.ParentCategoryIdFk AS ParentID, c.Name
FROM CategoryHierarchy h
INNER JOIN Category c
    ON c.ID = h.ID
ORDER BY h.SubTreeID ASC, h.Level DESC

This should get you results similar to the following:

ID | ParentID | Name
---+----------+----------
1  |     NULL | Men
2  |        1 | Shoes
3  |        2 | Sport
---+----------+----------
1  |     NULL | Men
2  |        1 | Shoes
4  |        2 | Casual
---+----------+----------
1  |     NULL | Men
5  |        1 | Watches
---+----------+----------
6  |     NULL | Women
10 |        6 | Watches

Of course the actual results won't have separators like that, I've added those to make the meaning of the results clearer.

If you don't want it completely exploded like this, you can use another rownum to only return the first instance of each parent:

WITH CategoryHierarchy AS
(
    SELECT
        ID, ParentCategoryIdFk, 0 AS Level,
        ROW_NUMBER() OVER (ORDER BY ID) AS SubTreeID
    FROM Category
    WHERE CategoryID IN
    (
        SELECT pc.CategoryID
        FROM Sale s
        INNER JOIN Product p
            ON p.saleidfk = s.id
        INNER JOIN ProductCategory pc
            ON pc.productid = p.id
        WHERE s.id = @SaleID
    )

    UNION ALL

    SELECT c.ID, c.ParentCategoryIdFk, h.Level + 1, h.SubTreeID
    FROM CategoryHierarchy h
    INNER JOIN Category c
        ON c.ID = h.ParentID
),
Filter_CTE AS
(
    SELECT
        ID, Level, SubTreeID
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SubTreeID) AS RowNum
    FROM CategoryHierarchy
)
SELECT c.ID, c.ParentCategoryIdFk AS ParentID, c.Name
FROM Filter_CTE f
INNER JOIN Category c
    ON c.ID = f.ID
WHERE f.RowNum = 1
ORDER BY f.SubTreeID ASC, f.Level DESC

...will give you results similar to:

ID | ParentID | Name
---+----------+----------
1  |     NULL | Men
2  |        1 | Shoes
3  |        2 | Sport
4  |        2 | Casual
5  |        1 | Watches
6  |     NULL | Women
10 |        6 | Watches

Note: Be careful with the second version, as it is not necessarily guaranteed to return results in hierarchical order. It just so happens that this version does because the IDs themselves are in hierarchical order. You can get around this limitation, but it would add a lot more complexity to this already-somewhat-complex query.

The second version does guarantee that a master category will always appear before any of its subcategories, which is fine if you plan to build a recursive data structure using a dictionary. It just might not be suitable for faster stack-based tree building or direct-to-user reporting. For those purposes, you would want to use the first version instead.

回复 原文
空袭的梦i 2024-09-04 15:15:46

有点混乱，但是：

DROP TABLE #Sale
GO
DROP TABLE #PRoduct
GO
DROP TABLE #ProductCategory
GO
DROP TABLE #Category
GO
CREATE TABLE #Sale 
(
    ID INT,
    Name VARCHAR(20)        
    )
GO
INSERT INTO #Sale SELECT 1, 'Sale1'
GO
CREATE TABLE #Product 
(
ID INT,
saleidfk INT,
name VARCHAR(20)
)
GO
INSERT INTO #Product 
SELECT 1,1,'a'
UNION
SELECT 2,1,'b'
UNION
SELECT 3,1,'c'
UNION
SELECT 4,1,'d'
UNION
SELECT 5,1,'e'
UNION
SELECT 6,1,'f'
GO
CREATE TABLE #ProductCategory 
(
ProductID INT,
CategoryID INT
)
GO
INSERT INTO #ProductCategory
SELECT 1,3
UNION
SELECT 2,3
UNION
SELECT 3,4
UNION
SELECT 4,5
UNION
SELECT 5,10
UNION
SELECT 6,10
GO
CREATE TABLE #Category 
(
ID INT,
ParentCategoryFK INT,
Name varchar(20)
)
GO
INSERT INTO #Category
SELECT 1,NULL,'Men'
UNION
SELECT 2,1,'Shoes'
UNION
SELECT 3,2,'Sport'
UNION
SELECT 4,2,'Casual'
UNION
SELECT 5,1,'Watches'
UNION
SELECT 6,NULL,'Women'
UNION
SELECT 7,6,'Shoes'
UNION
SELECT 8,7,'Sport'
UNION
SELECT 9,7,'Casual'
UNION
SELECT 10,6,'Watches'


GO

WITH Categories (CategoryName,CategoryID, [Level], SortOrder)  AS 
( 
    SELECT  Cat.Name,cat.id, 1 AS [Level], CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (order by cat.Name) ) AS SortOrder
    FROM #Category Cat 
    WHERE Cat.ParentCategoryFK IS NULL 

    UNION ALL

    SELECT CAT.Name,cat.ID, [Level] + 1, c2.SortOrder + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (order by cat.Name)) 
    FROM #Category CAT 
    INNER JOIN Categories c2 ON cat.ParentCategoryFK = c2.CategoryID
) 
SELECT #Sale.Name, Categories.CategoryName, #Product.name,Categories.Level,Categories.SortOrder FROM
Categories 
LEFT JOIN
#ProductCategory ON #ProductCategory.CategoryID = Categories.CategoryID
LEFT JOIN
#Product ON #Product.ID = #ProductCategory.ProductID
LEFT JOIN
#Sale ON #Product.saleidfk = #Sale.ID
ORDER BY Categories.SortOrder, #Product.name

需要注意的相关点是，为了使完整的层次结构有意义，您需要类别，无论它们是否有产品。此外，SortOrder 的 varchar 允许层次结构以正确的顺序显示。

Is a bit messy, but:

DROP TABLE #Sale
GO
DROP TABLE #PRoduct
GO
DROP TABLE #ProductCategory
GO
DROP TABLE #Category
GO
CREATE TABLE #Sale 
(
    ID INT,
    Name VARCHAR(20)        
    )
GO
INSERT INTO #Sale SELECT 1, 'Sale1'
GO
CREATE TABLE #Product 
(
ID INT,
saleidfk INT,
name VARCHAR(20)
)
GO
INSERT INTO #Product 
SELECT 1,1,'a'
UNION
SELECT 2,1,'b'
UNION
SELECT 3,1,'c'
UNION
SELECT 4,1,'d'
UNION
SELECT 5,1,'e'
UNION
SELECT 6,1,'f'
GO
CREATE TABLE #ProductCategory 
(
ProductID INT,
CategoryID INT
)
GO
INSERT INTO #ProductCategory
SELECT 1,3
UNION
SELECT 2,3
UNION
SELECT 3,4
UNION
SELECT 4,5
UNION
SELECT 5,10
UNION
SELECT 6,10
GO
CREATE TABLE #Category 
(
ID INT,
ParentCategoryFK INT,
Name varchar(20)
)
GO
INSERT INTO #Category
SELECT 1,NULL,'Men'
UNION
SELECT 2,1,'Shoes'
UNION
SELECT 3,2,'Sport'
UNION
SELECT 4,2,'Casual'
UNION
SELECT 5,1,'Watches'
UNION
SELECT 6,NULL,'Women'
UNION
SELECT 7,6,'Shoes'
UNION
SELECT 8,7,'Sport'
UNION
SELECT 9,7,'Casual'
UNION
SELECT 10,6,'Watches'


GO

WITH Categories (CategoryName,CategoryID, [Level], SortOrder)  AS 
( 
    SELECT  Cat.Name,cat.id, 1 AS [Level], CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (order by cat.Name) ) AS SortOrder
    FROM #Category Cat 
    WHERE Cat.ParentCategoryFK IS NULL 

    UNION ALL

    SELECT CAT.Name,cat.ID, [Level] + 1, c2.SortOrder + CONVERT(VARCHAR(MAX), ROW_NUMBER() OVER (order by cat.Name)) 
    FROM #Category CAT 
    INNER JOIN Categories c2 ON cat.ParentCategoryFK = c2.CategoryID
) 
SELECT #Sale.Name, Categories.CategoryName, #Product.name,Categories.Level,Categories.SortOrder FROM
Categories 
LEFT JOIN
#ProductCategory ON #ProductCategory.CategoryID = Categories.CategoryID
LEFT JOIN
#Product ON #Product.ID = #ProductCategory.ProductID
LEFT JOIN
#Sale ON #Product.saleidfk = #Sale.ID
ORDER BY Categories.SortOrder, #Product.name

The pertinent points to notice are that to get the full hierarchy to make sense, you need the categories whether they have products or not. Also the varchar for the SortOrder allows the hierarchy to display in the correct order.

回复 原文
ゃ懵逼小萝莉 2024-09-04 15:15:46

我想我已经太晚了，但对于未来尝试同样做法的同行来说，我认为这会起作用。 :)（只是针对一个特定项目的父层次结构执行此操作，但与叶子的内部连接将执行相同的操作）

with 
hierarchy (id, parentId, level)
as
(
    select c.id, c.parentId, 0 as level
    from categories c
    where parentId = 0
    union all
    select c.id, c.parentId, level + 1
    from categories c
    inner join hierarchy p on c.parentId = p.id
),
parents (id, parentId, level)
as
(
    select l.id, l.parentId, l.level
    from hierarchy l
        [where id = *leafid* | inner join *insert_your_leaves_here*]
    union all
    select p.id, p.parentId, p.level
    from hierarchy p
    inner join parents l on p.id = l.parentId
)

select * from parents

I guess I'm too late, but for future peers trying the same, I think this will work. :) (Just did it for one specific item's parent hierarchy, but an inner join with leaves will do the same trick)

with 
hierarchy (id, parentId, level)
as
(
    select c.id, c.parentId, 0 as level
    from categories c
    where parentId = 0
    union all
    select c.id, c.parentId, level + 1
    from categories c
    inner join hierarchy p on c.parentId = p.id
),
parents (id, parentId, level)
as
(
    select l.id, l.parentId, l.level
    from hierarchy l
        [where id = *leafid* | inner join *insert_your_leaves_here*]
    union all
    select p.id, p.parentId, p.level
    from hierarchy p
    inner join parents l on p.id = l.parentId
)

select * from parents
回复 原文
~没有更多了~

关于作者

晨曦慕雪

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

尘世孤行

文章 0 评论 0

烟─花易冷

文章 0 评论 0

你是年少的欢喜

文章 0 评论 0

倒带

文章 0 评论 0

忱杏

文章 0 评论 0

送君千里

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文