一道SQL面试题

一道,sql,面试题 · 浏览次数 : 9

小编点评

```sql WITH T_RecurAS ( SELECT Id, 1 AS num, cast(name AS nvarchar(512)) AS src, name AS l1, cast('') AS l2, cast('') AS l3, cast('') AS l4, cast('') AS l5, cast('') AS l6, cast('') AS l7 FROM [TestDB].[dbo].[Recursion] WHERE Pid IS NULL UNION ALL SELECT r.Id, t.num + 1 AS num, cast(t.src + r.Name AS nvarchar(512)) AS src, t.l1, CASE WHEN t.num = 1 THEN r.name WHEN t.num > 1 THEN t.l2 WHEN t.num > 2 THEN t.l3 WHEN t.num > 3 THEN t.l4 WHEN t.num > 4 THEN t.l5 WHEN t.num > 5 THEN t.l6 WHEN t.num > 6 THEN t.l7 END AS l2, CASE WHEN t.num = 2 THEN r.name WHEN t.num > 2 THEN t.l3 WHEN t.num > 3 THEN t.l4 WHEN t.num > 4 THEN t.l5 WHEN t.num > 5 THEN t.l6 WHEN t.num > 6 THEN t.l7 END AS l3, CASE WHEN t.num = 3 THEN r.name WHEN t.num > 3 THEN t.l4 WHEN t.num > 4 THEN t.l5 WHEN t.num > 5 THEN t.l6 WHEN t.num > 6 THEN t.l7 END AS l4, CASE WHEN t.num = 4 THEN r.name WHEN t.num > 4 THEN t.l5 WHEN t.num > 5 THEN t.l6 WHEN t.num > 6 THEN t.l7 END AS l5, CASE WHEN t.num = 5 THEN r.name WHEN t.num > 5 THEN t.l6 WHEN t.num > 6 THEN t.l7 END AS l6, CASE WHEN t.num = 6 THEN r.name WHEN t.num > 6 THEN t.l7 END AS l7 ) SELECT l1 '一级部门', l2 '二级部门', l3 '三级部门', l4 '四级部门', l5 '五级部门', l6 '六级部门', l7 '七级部门' FROM T_Recur; ```

正文

表结构如下

是一张递归格式的表

使用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 
    ,t.l1
    ,CASE WHEN t.num=1 THEN r.name 
    when t.num>1 Then t.l2
     ELSE cast('' as nvarchar(16)) END 
    ,CASE WHEN t.num=2 THEN r.name 
    when t.num>2 Then t.l3
    ELSE cast('' as nvarchar(16)) END 
    ,CASE WHEN t.num=3 THEN r.name
    when t.num>3 Then t.l4 
     ELSE cast('' as nvarchar(16)) END 
    ,CASE WHEN t.num=4 THEN r.name
    when t.num>4 Then t.l5
     ELSE cast('' as nvarchar(16)) END 
    ,CASE WHEN t.num=5 THEN r.name
    when t.num>5 Then t.l6
     ELSE cast('' as nvarchar(16)) END 
    ,CASE WHEN t.num=6 THEN r.name
    when t.num>6 Then t.l7 
     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实现。