MS SQLSERVER中包含面包屑样式的类别列表，如Opencart [英] Category list with bread crumb style in MS SQLSERVER, like Opencart

问题描述

我在MS SQL SERVER中有表结构，如下所示

Hi,

I have table structure in MS SQL SERVER as following

Table Name: PSCategories

PSId      | PSName         | ParentId(FK to PSCategories(PSId) )
1         | test1          | NULL
2         | test2          | 1
3         | test3          | NULL
4         | test4          | 3
5         | test5          | 4

我希望上表的输出显示如下

I want the output of above table to be show as following

PSId     | PSName                  | ParentId
1        | test1                   | 0
2        | test1 > test2           | 1
3        | test3                   | 0
4        | test3 > test4           | 3
5        | test3 > test4 > test5   | 4

如何在SQL SERVER中使用查询实现此目的？



------- -------------------------------------------------- ------------



我在Open-cart ADMIN系统中使用以下查询在MY SQL中找到的类似结果

How can I achieve this using query in SQL SERVER?

---------------------------------------------------------------------

The similar type of result I found in Open-cart ADMIN system using following query in MY SQL

SELECT 
	cp.category_id AS category_id, 
	GROUP_CONCAT(c.name ORDER BY cp.level SEPARATOR ' > ') AS name, 
	c.parent_id, 
	c.sort_order 
FROM 
	oc_category_path cp LEFT JOIN oc_category c 
	ON (cp.path_id = c.category_id)  
GROUP BY 
	cp.category_id 
ORDER BY cp.category_id

表结构如下

The table structure there is as follow

Table: oc_Category
Category_Id |  Name | ParentId (FK to oc_Category(Category_Id))

Table: oc_category_path 
Category_Id |  PathId(FK to oc_Category(Category_Id))

由于我的表结构不相似但有些相同，需要实现只使用1个表，请帮我生成查询对于MS SQLSERVER。



谢谢。

As my table structure is not similar but some what same, and need to achieve using 1 table only, please help me generating query for MS SQLSERVER.

Thanks.

推荐答案

你好。

试试这个。

Hello.
Try this one.

WITH categories (PSId, PSName, ParentId)
  AS (SELECT PSId, CAST(PSName AS VARCHAR(255)), 0
        FROM PSCategories AS pm
       WHERE pm.ParentId IS NULL
       UNION ALL
      SELECT ps.PSId, CAST(m.PSName + ' > ' + ps.PSName AS VARCHAR(255)), m.PSId
        FROM PSCategories AS ps
        JOIN categories AS m
          ON m.PSId = ps.ParentId
       WHERE ps.ParentId IS NOT NULL
       )
SELECT * FROM categories
ORDER BY PSId

这篇关于MS SQLSERVER中包含面包屑样式的类别列表，如Opencart的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！