sql数据读取问题
有一张类别表,需要根据类别表输出以下信息,详情见如下图片第一张图片是表结构图片,第二张图片是通过第一张表读取的数据,sql语句该怎么写? 也没说是啥数据库oracle,sql server提供了递归函数的,mysql没有 数据库类型决定sql怎么写 zero呆 发表于 2021-6-16 11:02
也没说是啥数据库oracle,sql server提供了递归函数的,mysql没有 数据库类型决定sql怎么写
sqlserver
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
页:
[1]