mysql - 类别和子类别sql顺序排序

Question

我有以下表格

表名:类别

id   name        parent  
16   Rash Vest   41  
17   Shorts      41  
20   Tops        41  
41   Shop        0  
47   Mens        16  
48   Womens      16  

表名:项目

id   title                 alias                 catid  
70   Rash Vest Black       rash-vest-black       47   
96   Rash Vest Red         rash-vest-red         47  
98   Rash Vest Womens Red  rash-vest-womens-red  48  

现在我想根据根树显示所有项目..

像这样
主菜单:商店
子菜单:Rash Vest |短裤 |上衣
子类别:
男装
----->黑色皮疹背心
----->Rash 背心红色
女士
----->Rash 背心女式红色

Answer 1

SQL-SERVER 版本:

;WITH groups AS
(SELECT ID
 , Name
 , ParentID
 , 0 AS Level
 , CAST(Name AS VARCHAR(255)) AS Path
 FROM Category WHERE ParentID = 0

 UNION ALL

 SELECT c.ID
 , c.Name
 , c.ParentID
 , g.Level + 1
 , CAST(CAST(g.Path AS VARCHAR(255)) + 
        CAST(' -> ' AS VARCHAR(255)) + 
        CAST(c.Name AS VARCHAR(255)) AS VARCHAR(255))
 FROM Category c 
 INNER JOIN groups g ON g.ID = c.ParentID
)

SELECT g.Name
, g.Level
, g.Path
, ISNULL(itm.Title, 'No items') AS Title
FROM groups g
LEFT JOIN Item itm ON itm.CategoryID = g.ID
ORDER BY g.Path

如果你想显示有问题的结果，而不是用户界面(你的应用程序)的这部分工作

SQL Fiddle例如/测试