浅析 SQL 数据分析题目——层次数据逐级展示

SQL干货分享 2021-08-13

699

（CSDN博主：写代码也要符合基本法）

各位看官老爷大家早上好，我是你们的老伙计小刘。最近我看到一道怪有趣的 SQL 题目，在此同大家探讨一下

（图片自网络）

我们先来看看题目是怎么样的：有如下省市县层次数据存于表 DEMO_LOCATIONS 中，要求按照图示形式展示数据，即以省市县分别展示三列，省级行没有市县两列，市级行没有县列

SQL> SELECT loc_code, loc_name, parent_code FROM demo_locations
  2  /


LOC_CODE   LOC_NAME   PARENT_CODE
---------- ---------- -----------
100000     山东       0
200000     广东       0
110000     菏泽       100000
120000     青岛       100000
210000     佛山       200000
220000     深圳       200000
111000     曹县       110000
112000     单县       110000
113000     巨野       110000


9 rows selected
复制

说起来题目倒是简单，要求的展示结果也没有难以理解的地方。但越是这种看起来简单的问题，实现起来却弯弯绕绕的越多

首先我们要解决的一点是，表中消除了省市县这个层次概念，也就是说这些地点数据都装在同一列里。题目看似是行转列，实则却不是，因为我们要的结果是在每一行中的不同三列里区分地点的层级，准确的说就是由一列“混合列”，拆分出省列、市列和县列

既然如此，我们先用最直接了当的办法搞出三列来直观的看看数据：复用例表，以其自连接的方式造出“省表”、“市表”和“县表”

SQL> SELECT t1.loc_name loc1, t2.loc_name loc2, t3.loc_name loc3
  2    FROM demo_locations t1, demo_locations t2, demo_locations t3
  3   WHERE t1.loc_code = t2.parent_code(+)
  4     AND t2.loc_code = t3.parent_code(+)
  5     AND t1.parent_code = '0'
  6  /


LOC1       LOC2       LOC3
---------- ---------- ----------
山东       菏泽       曹县
山东       菏泽       单县
山东       菏泽       巨野
山东       青岛       
广东       佛山       
广东       深圳       


6 rows selected
复制

虽然和最终结果还有差距，但已粗具规模，至少三列的层次已经尽数展现了

仔细观察上例由外连接得出的结果和题目要求的最终结果差在哪里？总的来说，差在两点：一是没有省行，也就是只有广东和山东的两行；二是缺少部分县行，具体来说就是没有 LOC3 列为空、LOC2 是菏泽的这一行

这当然是外连接本来的作用决定的：主表中的行即便关联不出外连接表里的数据，也要选择到结果集中。其隐含的意思是，关联不出的行都显示了，更别说能关联出来数据的行了，肯定是携手外连接表的数据一起显示了

所以说决定结果集数据的是主表，而不是外连接表

为什么没有只到菏泽市的这一行呢？便是因为表中确有菏泽市下辖县的数据。换句话说上例能展示出青岛、佛山和深圳三市才是外连接的功劳：虽然没有它们下辖县的数据，但 T3 表是外连接到 T2 表的，所以 T2 表有的数据都要展示，没有 T3 表数据的，LOC3 列置为空

没有单独省列的行也是同理，因为两省下辖市的数据都是有的，如果这里面再加个没有下辖市数据的省，比如日本，那么 T1 和 T2 表外连接的功效才能得以展现，只有日本能够单独成行

说到这里，一定会有老爷说：做题就做题，你嘚嘚外连接干什么？你这就是在凑字数

对，但不完全对

正所谓知己知彼，百战不殆。我们首先要知道上例和想要的结果到底差在哪，才能真正满足出题人变态的需求

闲话勾开，既然我们已经看透了上例的“缺”正是因为“有”，那么我们想要给它的结果集加行，就不是在数据源头下手，而是应该改造结果集本身

所以我们就想了：如果能把省的那行，它的市和县两列置空，同样把市的那行县给置空就好了

ROLLUP 一听：我四干介个的啊 → 传送门

说起来就是要把市列和县列依次置空，用 ROLLUP 就很趁手

GROUP BY t1.loc_name, ROLLUP(t2.loc_name, t3.loc_name)
复制

使用如上的分组规则，就能在不置空省列的前提下，多实现两种情形的分组：市列 + 置空的县列，以及置空的市列和县列

但是只做到这一步还不够，因为结果是下图这样的

问题看起来是只有省和市的行重复出现了，原因也很好想到：佛山、深圳、青岛三市的行在原结果中就有，如今出现的两行分别是本来 LOC3 就为空的行（外连接所得）跟置空 LOC3 的行（分组所得）

而菏泽那一行正是我们想通过 ROLLUP 分组造的一行

从而我们得出完善这个方案的筛选条件：保留所有 LOC3 被置空的行，以及那些 LOC3 不是被置空的行，条件是其 LOC3 本不为空

SQL> SELECT t1.loc_name loc1, t2.loc_name loc2, t3.loc_name loc3
  2    FROM demo_locations t1, demo_locations t2, demo_locations t3
  3   WHERE t1.parent_code = '0'
  4     AND t1.loc_code = t2.parent_code(+)
  5     AND t2.loc_code = t3.parent_code(+)
  6   GROUP BY t1.loc_name, ROLLUP(t2.loc_name, t3.loc_name)
  7  HAVING grouping(t3.loc_name) = 1 OR t3.loc_name IS NOT NULL
  8   ORDER BY loc1, loc2 NULLS FIRST, loc3 NULLS FIRST
  9  /


LOC1       LOC2       LOC3
---------- ---------- ----------
广东                  
广东       佛山       
广东       深圳       
山东                  
山东       菏泽       
山东       菏泽       曹县
山东       菏泽       单县
山东       菏泽       巨野
山东       青岛       


9 rows selected
复制

效果还不错

不过，我们似乎还能再想想别的法子，以便让我们的 SQL 更能灵活应对数据增加，尤其是数据层次加深的情况

数据表本来是有层次结构的，这很难不让我们往层次查询上面去联想。事实上层次查询只是表数据访问次序的一种改变，不会制造不存在的行（当然，根据具体情况，同样的行可能被展示超过一次），这其实挺符合题目的需要：表里这一行是省，那就放在省列；这行是市，那就放在市列，顺带显示出其省；至于县行，则是顺带显示出其省市

所以重新观察题目要求的展示结果，不难感受到每行的数据，都和层次查询的路径是吻合的

查询路径？SYS_CONNECT_BY_PATH 一听：我四干介个的啊 → 传送门

所以曲线救国，先得出一个路径，再将路径拆成三个字段

SQL> WITH conn AS
  2   (SELECT sys_connect_by_path(loc_name, '.') p
  3      FROM demo_locations
  4    CONNECT BY PRIOR loc_code = parent_code
  5     START WITH parent_code = '0')
  6  SELECT regexp_substr(p, '[^\.]+', 1, 1) loc1
  7        ,regexp_substr(p, '[^\.]+', 1, 2) loc2
  8        ,regexp_substr(p, '[^\.]+', 1, 3) loc3
  9    FROM conn
 10  /


LOC1       LOC2       LOC3
---------- ---------- ----------
山东                  
山东       菏泽       
山东       菏泽       曹县
山东       菏泽       单县
山东       菏泽       巨野
山东       青岛       
广东                  
广东       佛山       
广东       深圳       


9 rows selected
复制

拆开查询路径是曲线救国，可小刘更喜欢长枪直入，如果使用递归子查询，直接在每一行安顿三列的数据岂不美哉 → 传送门

对于递归中的每一层，第一层就是省，那么 LOC_NAME 就放到 LOC1；第二层是市，那么 LOC_NAME 放到 LOC2，LOC1 则是沿用上一层；第三层是县，LOC_NAME 放到 LOC3，前两列沿用上一层

SQL> WITH conn(lv, loc_code, loc1, loc2, loc3) AS
  2   (SELECT 1 lv, loc_code, loc_name loc1, NULL loc2, NULL loc3
  3      FROM demo_locations
  4     WHERE parent_code = '0'
  5    UNION ALL
  6    SELECT c.lv + 1 lv
  7          ,l.loc_code
  8          ,c.loc1
  9          ,decode(c.lv + 1, 2, l.loc_name, c.loc2) loc2
 10          ,decode(c.lv + 1, 3, l.loc_name, c.loc3) loc3
 11      FROM conn c, demo_locations l
 12     WHERE c.loc_code = l.parent_code)
 13  SELECT loc1, loc2, loc3 FROM conn ORDER BY loc_code
 14  /


LOC1       LOC2       LOC3
---------- ---------- ----------
山东                  
山东       菏泽       
山东       菏泽       曹县
山东       菏泽       单县
山东       菏泽       巨野
山东       青岛       
广东                  
广东       佛山       
广东       深圳       


9 rows selected
复制

好了，最后我们再来速览一下当数据层次结构加深后，三个 SQL 方案的改动量情况

SQL> SELECT t1.loc_name loc1
  2        ,t2.loc_name loc2
  3        ,t3.loc_name loc3
  4        ,t4.loc_name loc4
  5    FROM demo_locations t1
  6        ,demo_locations t2
  7        ,demo_locations t3
  8        ,demo_locations t4
  9   WHERE t1.parent_code = '0'
 10     AND t1.loc_code = t2.parent_code(+)
 11     AND t2.loc_code = t3.parent_code(+)
 12     AND t3.loc_code = t4.parent_code(+)
 13   GROUP BY t1.loc_name, ROLLUP(t2.loc_name, t3.loc_name, t4.loc_name)
 14  HAVING grouping_id(t3.loc_name, t4.loc_name) = 3 OR
 15         grouping_id(t3.loc_name, t4.loc_name) = 1 AND t3.loc_name IS NOT NULL OR
 16         t4.loc_name IS NOT NULL
 17   ORDER BY loc1, loc2 NULLS FIRST, loc3 NULLS FIRST, loc4 NULLS FIRST
 18  /


LOC1       LOC2       LOC3       LOC4
---------- ---------- ---------- ----------
广东                             
广东       佛山                  
广东       深圳                  
山东                             
山东       菏泽                  
山东       菏泽       曹县       
山东       菏泽       曹县       牛逼
山东       菏泽       单县       
山东       菏泽       巨野       
山东       青岛                  


10 rows selected


SQL> WITH conn AS
  2   (SELECT sys_connect_by_path(loc_name, '.') p
  3      FROM demo_locations
  4    CONNECT BY PRIOR loc_code = parent_code
  5     START WITH parent_code = '0')
  6  SELECT regexp_substr(p, '[^\.]+', 1, 1) loc1
  7        ,regexp_substr(p, '[^\.]+', 1, 2) loc2
  8        ,regexp_substr(p, '[^\.]+', 1, 3) loc3
  9        ,regexp_substr(p, '[^\.]+', 1, 4) loc4
 10    FROM conn
 11  /


LOC1       LOC2       LOC3       LOC4
---------- ---------- ---------- ----------
山东                             
山东       菏泽                  
山东       菏泽       曹县       
山东       菏泽       曹县       牛逼
山东       菏泽       单县       
山东       菏泽       巨野       
山东       青岛                  
广东                             
广东       佛山                  
广东       深圳                  


10 rows selected


SQL> WITH conn(lv, loc_code, loc1, loc2, loc3, loc4) AS
  2   (SELECT 1 lv, loc_code, loc_name loc1, NULL loc2, NULL loc3, NULL loc4
  3      FROM demo_locations
  4     WHERE parent_code = '0'
  5    UNION ALL
  6    SELECT c.lv + 1 lv
  7          ,l.loc_code
  8          ,c.loc1
  9          ,decode(c.lv + 1, 2, l.loc_name, c.loc2) loc2
 10          ,decode(c.lv + 1, 3, l.loc_name, c.loc3) loc3
 11          ,decode(c.lv + 1, 4, l.loc_name, c.loc4) loc4
 12      FROM conn c, demo_locations l
 13     WHERE c.loc_code = l.parent_code)
 14  SELECT loc1, loc2, loc3, loc4 FROM conn ORDER BY loc_code
 15  /


LOC1       LOC2       LOC3       LOC4
---------- ---------- ---------- ----------
山东                             
山东       菏泽                  
山东       菏泽       曹县       
山东       菏泽       曹县       牛逼
山东       菏泽       单县       
山东       菏泽       巨野       
山东       青岛                  
广东                             
广东       佛山                  
广东       深圳                  


10 rows selected
复制

以上就是我们今天分享的全部内容了，看官老爷们藏龙卧虎，想必有更好的解决方案，请大佬不吝赐教

另外，今天方案一的 SQL 小刘写的其实是有 bug 的（提示：出现光杆省的情况），给大家留作思考的空间

数据库

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

浅析 SQL 数据分析题目——层次数据逐级展示

评论