多层嵌套的产品类别与asp.net和SQL Server显示？ [英] Multilevel nested product categories display with asp.net and sql server?

问题描述

我有一个产品类别表具有以下字段：

I have a product category table with the following fields:

CAT_ID（PK）

cat_id (PK)

Cat_name

Cat_desc

Parent_Cat_Id

Parent_Cat_Id

现在，当用户想增加一个产品，他应该可以选择多个类别从列表框（多选enabaled）。但是，为了让用户知道类别的层次，我需要在下面的样式来显示它们：

Now when a user wants to add a product he should be able to select multiple categories from a listbox(multiselection enabaled). But to let the user know the hierarchy of the categories, I need to display them in the following style:

父类1

父类1->子类别1

父类1->子类别1->子子类别1

parent category 1->sub category 1->sub-sub category 1

父类1->子类别1->子子类2

parent category 1->sub category 1->sub-sub category 2

父类1->子类别2

父类2

...

我知道，要做到这一点我需要使用递归程序。但是，如何才能通过存储过程呢？这将是更有效的，对吧？

I know that to achieve this I need to use recursive programming. But how can we do it via stored procedures? That would be much more efficient, right?

在预先感谢你的帮助。

推荐答案

下面就是你想要的。该存储过程使用了CTE或公用表前pression。我不是在他们的亲，但他们是非常有用的。 Definatly建议找他们。

Here's what you want. This stored procedure uses a "CTE" or "common table expression". I'm not a pro at them but they're very usefull. Definatly recommend looking them up.

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetRecursiveTest
AS
BEGIN
    ;WITh Tree (cat_id, cat_name, parent_cat_id, level, Sort) AS
    (
        SELECT cat_id, cat_name, parent_cat_id, 0 AS level, 
            CONVERT(varchar(255), cat_name) AS Sort 
        FROM RecursiveTest
        WHERE parent_cat_id = 0

        UNION ALL

    SELECT RT.cat_id, RT.cat_name, RT.parent_cat_id, Parent.level + 1 AS level, 
        CONVERT(varchar(255), Parent.Sort + ' -> ' + RT.cat_name) AS Sort
        FROM RecursiveTest RT
        INNER JOIN Tree as Parent ON Parent.cat_id = RT.parent_cat_id
    )

    SELECT Sort FROM Tree
    ORDER BY Sort
END
GO

其实，如果你改变最后的选择SELCT *从一棵树，你会得到关于层级更多的数据。

Actually, if you change that last select to "selct * from tree" you'll get more data about the hierarchy.

下面是一个链接到一个很好的资源<一个href=\"http://www.databasejournal.com/features/mssql/article.php/10894_3502676_1/Common-Table-Ex$p$pssions-CTE-on-SQL-2005.htm\"相对=nofollow> CTE的

Here's a link to a good resource for CTE's

这篇关于多层嵌套的产品类别与asp.net和SQL Server显示？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！