SQL自联接查询?如何获得类别子类别?

问题描述

我有一个数据库表，其格式如下

I've a database table, which has the following form

catID | category      | parentID
1     | firstCategory | null
2     | SubCategory1  | 1
3     | SubCategory2  | 1
4     | subSub1       | 3

以此类推...

有多个级别的类别.可以使用哪种查询来获取以下格式的记录:

There are multiple level of categories. What query can be used to get the records in the following format:

catID | category
1     | firstCategory
2     | firstCategory/SubCategory1
3     | firstCategory/SubCategory2
4     | firstCategory/SubCategory2/subSub1

类别ID将是最后一个类别的ID.如何编写查询以将类别添加到各个级别?不同类别的确切级别数不同?

The category id will be the id of the last category. How to write a query to join the categories to all levels? The exact number of levels for different categories is different?

我正在使用mySQL.

I'm using mySQL.

推荐答案

最大深度为6(包括根)，您可以使用此

For a maximum depth of 6 (including root), you can use this

select l0.catID,
    concat(
      case when l5.catID is null then '' else concat(l5.category, '/') end
    , case when l4.catID is null then '' else concat(l4.category, '/') end
    , case when l3.catID is null then '' else concat(l3.category, '/') end
    , case when l2.catID is null then '' else concat(l2.category, '/') end
    , case when l1.catID is null then '' else concat(l1.category, '/') end
    , l0.category)
from catcat l0
left join catcat l1 on l0.parentID=l1.catID
left join catcat l2 on l1.parentID=l2.catID
left join catcat l3 on l2.parentID=l3.catID
left join catcat l4 on l3.parentID=l4.catID
left join catcat l5 on l4.parentID=l5.catID

根据需要扩展图案以获得更长的最大深度.

Expand the pattern as required for longer max depths.

这篇关于SQL自联接查询?如何获得类别子类别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！