来看看这sql语句该如何写

来看看这sql语句该怎么写


如图是全国各省市的行政编号及名称
我的需求是 多出来2列显示该县市对应的上一级行政编号及名称
例如 330100 杭州市 330000 浙江省
          330185 临安市 330100 杭州市

------解决思路----------------------
应该可以取后三位 然后自己去构造一个ID。

后三位对100求余，取整可以得到层级。
------解决思路----------------------

With table1(CodeId, CodeName) AS (

    SELECT '330000','浙江省' UNION ALL

    SELECT '330100','杭州市' UNION ALL

    SELECT '330101','市辖区' UNION ALL

    SELECT '330185','临安市'

)

,t AS (

    -- 市

    SELECT c.*,

           p.CodeId PCodeId,

           p.CodeName PCodeName

      FROM table1 c

      JOIN table1 p

        ON p.CodeId LIKE '__0000'

       AND LEFT(p.CodeId,2) = LEFT(c.CodeId,2)

     WHERE c.CodeId NOT LIKE '__0000'

       AND c.CodeId LIKE '____00'

    UNION ALL -- 县

    SELECT c.*,

           p.CodeId PCodeId,

           p.CodeName PCodeName

      FROM table1 c

      JOIN table1 p

        ON p.CodeId LIKE '____00'

       AND LEFT(p.CodeId,4) = LEFT(c.CodeId,4)

     WHERE c.CodeId NOT LIKE '____00'

)

SELECT * FROM t ORDER BY CodeID

CodeId CodeName PCodeId PCodeName

------ -------- ------- ---------

330100 杭州市   330000  浙江省

330101 市辖区   330100  杭州市

330185 临安市   330100  杭州市