sql数据读取问题

lll333lll20

有一张类别表，需要根据类别表输出以下信息，详情见如下图片





第一张图片是表结构图片，第二张图片是通过第一张表读取的数据，sql语句该怎么写？

zero呆

也没说是啥数据库  oracle,sql server提供了递归函数的,mysql没有 数据库类型决定sql怎么写

lll333lll20

zero呆 发表于 2021-6-16 11:02
也没说是啥数据库  oracle,sql server提供了递归函数的,mysql没有 数据库类型决定sql怎么写

sqlserver

243634473

[SQL] 纯文本查看 复制代码

select a.name as 名称,
    (case
when a.treelevel = 0 then a.id
when a.treelevel = 1 then a.parentid
when a.treelevel = 2 then (select b.parentid from m_class b where a.parentid = b.id)
when a.treelevel = 3 then (select c.parentid from m_class c where c.id= (select b.parentid from m_class b where a.parentid = b.id))
else ''
end) as 根,
    (case
when a.treelevel = 0 then ''
when a.treelevel = 1 then a.id
when a.treelevel = 2 then a.parentid
when a.treelevel = 3 then  (select b.parentid from m_class b where a.parentid = b.id)
else ''
end) as 一级,
    (case
when a.treelevel = 0 then ''
when a.treelevel = 1 then ''
when a.treelevel = 2 then a.id
when a.treelevel = 3 then a.parentid
else ''
end) as 二级,
    (case
when a.treelevel = 0 then ''
when a.treelevel = 1 then ''
when a.treelevel = 2 then ''
when a.treelevel = 3 then a.id
else ''
end) as 三级
       from m_class a