这道 Mysql 的解题思想，值得学习！

Wu_Candy

发布于 2022-07-05 14:12:40

2800

发布于 2022-07-05 14:12:40

文章被收录于专栏：无量测试之道

题目:

(1). 前置条件：

有如下数据集A，表查询结果如下图所示，设置该表为表：test_province_city。

(2). 题目要求：

数据集A，按照省市区分别显示为3列，为省一级时，后面没有下一级，则后两列为空，为市一级，后面没有下一级，则后一列为空。

预期结果:

解题思路:

(1). 第1个union all的子查询，查询出省一级的信息

查询结果如下:

NAME_1   NAME_2  NAME_3
浙江   
安徽

(2). 第2个union all的子查询，查询出市二级的信息

查询结果如下：

NAME_1  NAME_2  NAME_3
浙江     杭州 
浙江     宁波 
安徽     合肥 
安徽     宣城

(3). 第3个union all的子查询，查询出区三级的信息

查询结果如下:

NAME_1   NAME_2   NAME_3
浙江      杭州      滨江
浙江      杭州      富阳
浙江      杭州      萧山

(4). 最后按照省、市NAME进行正序排列

查询结果如预期结果所示

解题方式一:

适用于不用创建物理表的情况下

使用数据集A,B,C,D,E,F替代物理表：test_province_city，直接复制以下 Mysql 语句，可以在 Mysql 或 Hive sql 环境直接运行，得到以上预期结果数据。

select NAME_1,
    NAME_2,
    NAME_3 
 from (select NAME AS NAME_1,
     '' AS NAME_2,
     '' AS NAME_3
   from (  select 1 ID,10000 CODE,'浙江' NAME,0 PARENTCODE
     union all
     select 2 ID,20000 CODE,'安徽' NAME,0 PARENTCODE
     union all
     select 3 ID,11000 CODE,'杭州' NAME,10000 PARENTCODE
     union all
     select 4 ID,12000 CODE,'宁波' NAME,10000 PARENTCODE
     union all
     select 5 ID,21000 CODE,'合肥' NAME,20000 PARENTCODE
     union all
     select 6 ID,22000 CODE,'宣城' NAME,20000 PARENTCODE
     union all
     select 7 ID,11100 CODE,'萧山' NAME,11000 PARENTCODE
     union all
     select 8 ID,11300 CODE,'滨江' NAME,11000 PARENTCODE
     union all
     select 9 ID,11300 CODE,'富阳' NAME,11000 PARENTCODE
    )A where PARENTCODE=0

    union all
    select B.NAME AS NAME_1,
        C.NAME AS NAME_2,
        '' AS NAME_3
     from (  select 1 ID,10000 CODE,'浙江' NAME,0 PARENTCODE
       union all
       select 2 ID,20000 CODE,'安徽' NAME,0 PARENTCODE
       union all
       select 3 ID,11000 CODE,'杭州' NAME,10000 PARENTCODE
       union all
       select 4 ID,12000 CODE,'宁波' NAME,10000 PARENTCODE
       union all
       select 5 ID,21000 CODE,'合肥' NAME,20000 PARENTCODE
       union all
       select 6 ID,22000 CODE,'宣城' NAME,20000 PARENTCODE
       union all
       select 7 ID,11100 CODE,'萧山' NAME,11000 PARENTCODE
       union all
       select 8 ID,11300 CODE,'滨江' NAME,11000 PARENTCODE
       union all
       select 9 ID,11300 CODE,'富阳' NAME,11000 PARENTCODE
      )B join 
      (   select 1 ID,10000 CODE,'浙江' NAME,0 PARENTCODE
       union all
       select 2 ID,20000 CODE,'安徽' NAME,0 PARENTCODE
       union all
       select 3 ID,11000 CODE,'杭州' NAME,10000 PARENTCODE
       union all
       select 4 ID,12000 CODE,'宁波' NAME,10000 PARENTCODE
       union all
       select 5 ID,21000 CODE,'合肥' NAME,20000 PARENTCODE
       union all
       select 6 ID,22000 CODE,'宣城' NAME,20000 PARENTCODE
       union all
       select 7 ID,11100 CODE,'萧山' NAME,11000 PARENTCODE
       union all
       select 8 ID,11300 CODE,'滨江' NAME,11000 PARENTCODE
       union all
       select 9 ID,11300 CODE,'富阳' NAME,11000 PARENTCODE
      )C on B.CODE = C.PARENTCODE and B.PARENTCODE=0

    union all
    select D.NAME AS NAME_1,
        E.NAME AS NAME_2, 
        F.NAME AS NAME_3
     from (  select 1 ID,10000 CODE,'浙江' NAME,0 PARENTCODE
       union all
       select 2 ID,20000 CODE,'安徽' NAME,0 PARENTCODE
       union all
       select 3 ID,11000 CODE,'杭州' NAME,10000 PARENTCODE
       union all
       select 4 ID,12000 CODE,'宁波' NAME,10000 PARENTCODE
       union all
       select 5 ID,21000 CODE,'合肥' NAME,20000 PARENTCODE
       union all
       select 6 ID,22000 CODE,'宣城' NAME,20000 PARENTCODE
       union all
       select 7 ID,11100 CODE,'萧山' NAME,11000 PARENTCODE
       union all
       select 8 ID,11300 CODE,'滨江' NAME,11000 PARENTCODE
       union all
       select 9 ID,11300 CODE,'富阳' NAME,11000 PARENTCODE
      )D JOIN 
      (   select 1 ID,10000 CODE,'浙江' NAME,0 PARENTCODE
       union all
       select 2 ID,20000 CODE,'安徽' NAME,0 PARENTCODE
       union all
       select 3 ID,11000 CODE,'杭州' NAME,10000 PARENTCODE
       union all
       select 4 ID,12000 CODE,'宁波' NAME,10000 PARENTCODE
       union all
       select 5 ID,21000 CODE,'合肥' NAME,20000 PARENTCODE
       union all
       select 6 ID,22000 CODE,'宣城' NAME,20000 PARENTCODE
       union all
       select 7 ID,11100 CODE,'萧山' NAME,11000 PARENTCODE
       union all
       select 8 ID,11300 CODE,'滨江' NAME,11000 PARENTCODE
       union all
       select 9 ID,11300 CODE,'富阳' NAME,11000 PARENTCODE
      )E ON D.CODE=E.PARENTCODE AND D.PARENTCODE=0
      JOIN (  select 1 ID,10000 CODE,'浙江' NAME,0 PARENTCODE
        union all
        select 2 ID,20000 CODE,'安徽' NAME,0 PARENTCODE
        union all
        select 3 ID,11000 CODE,'杭州' NAME,10000 PARENTCODE
        union all
        select 4 ID,12000 CODE,'宁波' NAME,10000 PARENTCODE
        union all
        select 5 ID,21000 CODE,'合肥' NAME,20000 PARENTCODE
        union all
        select 6 ID,22000 CODE,'宣城' NAME,20000 PARENTCODE
        union all
        select 7 ID,11100 CODE,'萧山' NAME,11000 PARENTCODE
        union all
        select 8 ID,11300 CODE,'滨江' NAME,11000 PARENTCODE
        union all
        select 9 ID,11300 CODE,'富阳' NAME,11000 PARENTCODE
       )F ON E.CODE=F.PARENTCODE
    )G ORDER BY NAME_1,NAME_2;

解题方式二：

适用于先创建物理表：test_province_city 的情况下

将测试数据 insert 至 test_province_city 表。

表数据结果如下：

将解题方式一中的数据集A,B,C,D,E,F替换成表 test_province_city即可。

select NAME_1,
       NAME_2,
       NAME_3 
 from (select NAME AS NAME_1,
                '' AS NAME_2,
                '' AS NAME_3
   from test_province_city A where PARENTCODE=0
    union all
    select B.NAME AS NAME_1,
        C.NAME AS NAME_2,
        '' AS NAME_3
     from test_province_city B join 
      test_province_city C on B.CODE = C.PARENTCODE and B.PARENTCODE=0
    union all
    select D.NAME AS NAME_1,
        E.NAME AS NAME_2, 
        F.NAME AS NAME_3
     from test_province_city D JOIN 
      test_province_city E ON D.CODE=E.PARENTCODE AND D.PARENTCODE=0
      JOIN test_province_city F ON E.CODE=F.PARENTCODE
    )G ORDER BY NAME_1,NAME_2;

解题思想归纳：

1.第1个子查询，查询出省一级的信息，查询结果如下：

NAME_1   NAME_2  NAME_3
浙江   
安徽

2.第2个子查询，查询出市二级的信息，查询结果如下：

NAME_1  NAME_2  NAME_3
浙江    杭州 
浙江    宁波 
安徽    合肥 
安徽    宣城

3.第3个子查询，查询出区三级的信息，查询结果如下：

NAME_1   NAME_2   NAME_3
浙江      杭州     滨江
浙江      杭州     富阳
浙江      杭州     萧山

最后将这3部分的查询结果进行union all，然后按照省、市的NAME进行正序排列，最终得到如期的预期结果。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2022-03-14，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

云数据库 SQL Server

sql

本文分享自无量测试之道微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

云数据库 SQL Server

sql

登录后参与评论

0 条评论

热度