表结构如下
是一张递归格式的表
使用SQL转换成如下格式
SQL实现
使用SQL转换成上图的格式
SQL代码：
WITH T_Recur
AS
(
SELECT Id,1 num, cast(name as nvarchar(512)) as src
, name as l1, cast('' as nvarchar(16)) l2, cast('' as nvarchar(16)) l3, cast('' as nvarchar(16)) l4
, cast('' as nvarchar(16)) l5, cast('' as nvarchar(16)) l6, cast('' as nvarchar(16)) l7
FROM [TestDB].[dbo].[Recursion] WHERE Pid is NULL
UNION all
SELECT r.Id, t.num+1 num,cast(t.src+r.Name as nvarchar(512)) as src
,cast(SUBSTRING(t.src,1,LEN(t.l1)) as nvarchar(16))
,CASE WHEN t.num=1 THEN r.name
when t.num>1 Then cast(SUBSTRING(t.src,LEN(t.l1)+1,LEN(t.l2)) as nvarchar(16))
ELSE cast('' as nvarchar(16)) END
,CASE WHEN t.num=2 THEN r.name
when t.num>2 Then cast(SUBSTRING(t.src,LEN(t.l1)+LEN(t.l2)+1,LEN(t.l3)) as nvarchar(16))
ELSE cast('' as nvarchar(16)) END
,CASE WHEN t.num=3 THEN r.name
when t.num>3 Then cast(SUBSTRING(t.src,LEN(t.l3)+LEN(t.l2)+LEN(t.l1)+1,LEN(t.l4)) as nvarchar(16))
ELSE cast('' as nvarchar(16)) END
,CASE WHEN t.num=4 THEN r.name
when t.num>4 Then cast(SUBSTRING(t.src,LEN(t.l4)+LEN(t.l3)+LEN(t.l2)+LEN(t.l1)+1,LEN(t.l5)) as nvarchar(16))
ELSE cast('' as nvarchar(16)) END
,CASE WHEN t.num=5 THEN r.name
when t.num>5 Then cast(SUBSTRING(t.src,LEN(t.l5)+LEN(t.l4)+LEN(t.l3)+LEN(t.l2)+LEN(t.l1)+1,LEN(t.l6)) as nvarchar(16))
ELSE cast('' as nvarchar(16)) END
,CASE WHEN t.num=6 THEN r.name
when t.num>7 Then cast(SUBSTRING(t.src,LEN(t.l6)+LEN(t.l5)+LEN(t.l4)+LEN(t.l3)+LEN(t.l2)+LEN(t.l1)+1,LEN(t.l7)) as nvarchar(16))
ELSE cast('' as nvarchar(16)) END
from [TestDB].[dbo].[Recursion] r JOIN T_Recur t on r.PId=t.Id
)
select l1 '一级部门', l2 '二级部门', l3 '三级部门', l4 '四级部门', l5 '五级部门', l6 '六级部门', l7 '七级部门'
from T_Recur -- 多送三个部门
结束
我觉得挺离谱的，这是线上的笔试，离开笔试界面会有警告⚠，我就没用开发工具写，相当于是手写SQL了。
我当时没写出来，好久没用case，都用错了。当时case的判断我是用的==,正确写法是单等号=。我当时就随便写了一段case代码和递归SQL，然后说明了一下实现思路用case+cte实现。