使用subCategory的ID进行分层类别排序 [英] Hierarchical Category sorting with subCategory's ID

问题描述

我在mssql中有关于分层类别排序的问题.我有在以下代码段中列出类别及其级别的sql代码.

I have problem about the hierarchical category sort in mssql.I have sql code that lists category with their level in the following code segment.

WITH TempCtgry (CtgryID, CtgryName, ParentCtgryId, Lvl)AS (SELECT CategoryID,CategoryName ,ParentCatID, 0 as Level FROM Category r WHERE r.ParentCatID IS NULL UNION ALL SELECT r1.CategoryID, r1.CategoryName, r1.ParentCatID, TempCtgry.Lvl+1 FROM Category r1 INNER JOIN TempCtgry on r1.ParentCatID= TempCtgry.CtgryID) SELECT * FROM TempCtgry

但是我想显示子类别的父母.例如:电视>>智能>>液晶电视

But I want to show sub category's parents.For Example: Television >> Smart >> Lcd Tv

我该怎么做?

感谢和问候！

推荐答案

随身携带即可构建它，就像计算级别一样:

Build it up as you go, just like counting levels:

WITH TempCtgry ( CtgryID, CtgryName, ParentCtgryId, Lvl ) AS (
  SELECT CategoryID, CategoryName, ParentCatID, 0 as Level,
    Convert( VarChar(1024), CategoryName ) as Path
    FROM Category r
    WHERE r.ParentCatID IS NULL
  UNION ALL
  SELECT r1.CategoryID, r1.CategoryName, r1.ParentCatID, TempCtgry.Lvl + 1,
    Convert( VarChar(1024), Path + ' >> ' + r1.CategoryName ) as Path
    FROM Category r1 INNER JOIN
      TempCtgry on r1.ParentCatID = TempCtgry.CtgryID
  )
  SELECT *
    FROM TempCtgry

要将路径显示为网页上的链接，您需要同时具有要显示的类别名称和在链接中使用的相应ID.一个简单的分隔符，使您可以使用字符串拆分功能，例如此，可简化显示代码:

To display the path as links on a web page, you'll want to have both the category names for display and the corresponding ids for use in the links. A simple separator that allows you to use a string split function, e.g. this, simplifies the display code:

WITH TempCtgry ( CtgryID, CtgryName, ParentCtgryId, Lvl ) AS (
  SELECT CategoryID, CategoryName, ParentCatID, 0 as Level,
    Convert( VarChar(1024), CategoryName ) as Path,
    Convert( VarChar(1024), CategoryID ) as PathIds
    FROM Category r
    WHERE r.ParentCatID IS NULL
  UNION ALL
  SELECT r1.CategoryID, r1.CategoryName, r1.ParentCatID, TempCtgry.Lvl + 1,
    Convert( VarChar(1024), Path + '|' + r1.CategoryName ) as Path,
    Convert( VarChar(1024), PathIds + '|' + Convert( VarChar(9), CategoryId ) ) as PathIds
    FROM Category r1 INNER JOIN
      TempCtgry on r1.ParentCatID = TempCtgry.CtgryID
  )
  SELECT *
    FROM TempCtgry

注意:这假定类别名称中不能出现竖线("|").

Note: This assumes that a vertical bar ("|") cannot occur in a category name.

ASP.NET代码将需要拆分两个路径字符串，并将它们一起向下移动以创建链接.方便的显示分隔符是»"(U + 00BB:右指向双角引号).

The ASP.NET code will need to split both path strings and walk down them together creating links as it goes. A convenient display separator is "»" (U+00BB: Right-Pointing Double Angle Quotation Mark).

可以将所有代码添加到查询中，以生成所需的HTML，但这不是它所属的地方.

It is possible to add all of the code to generate the required HTML into the query, but that isn't where it belongs.

这篇关于使用subCategory的ID进行分层类别排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！