行政区划数据数据库的设计(七)

最新推荐文章于 2024-10-16 14:28:32 发布

原创最新推荐文章于 2024-10-16 14:28:32 发布 · 3.3k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #action #go #脚本 #join

VB.NET应用点滴专栏收录该内容

120 篇文章

订阅专栏

作者水如烟分享了在设计行政区划数据数据库时的经验，修改了行政区划的全名并创建了一个存储过程，以输出所需信息。该数据库适合用于练习，未来可以进行更多分析，如历年数据对比、城市化进程研究等。下一篇文章将介绍行政区划程序的查询实现。

Author：水如烟

总目录：行政区划数据方案设计

上一篇，行政区划数据数据库的设计(六)

上文实现了把区划码按三个行政级别分成了六个类，分别是：

一级：省

二级：省直辖市、地区州盟

三级：市地辖区、县旗、省直辖县级市

至于这样的命名和分级对不对，我心里也没准，不过修改还是方便的，到时知错了改正就是了。

现在继续说附属部分的全名。

单单给出了区划码和名称，如：654221 额敏县，我们看着也只有干着急的份。需要更多的信息。如是654221 新疆维吾尔自治区塔城地区额敏县，这才是我们需要的，看着也乐意。

全名无非是由省+市+县的名称组成，也就是一级名称+二级名称+三级名称，这是约定俗成的，不知也有相关规定没有，这回我没有去找（应当已有国家标准规定）。

这个定义我可以用函数固定下来了：

△

[Action]. 全名

USE [ RegionalCodeWorks ]
GO
/* ***** 对象:  UserDefinedFunction [Action].[全名]    脚本日期: 09/21/2006 17:55:00 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      LzmTW
-- Create date: 20060921
-- Description: 取行政区全名
--=============================================
CREATE FUNCTION [ Action ] . [ 全名 ]
(
      @当前时间      [ Base ] . [ RegionalDate ]      = N ' Current '
    , @一级          nchar ( 2 )
    , @二级          nchar ( 2 )
    , @三级          nchar ( 2 )
)
RETURNS nvarchar ( 200 )
AS
BEGIN
     -- 初始化变量

     DECLARE    @全名          nvarchar ( 200 )
            , @一级全名      nvarchar ( 100 )
            , @二级全名      nvarchar ( 100 )
            , @三级全名      nvarchar ( 100 )

     SET @一级全名 = N ''
     SET @二级全名 = N ''
     SET @三级全名 = N ''

     DECLARE    @一级区划码      [ Base ] .RegionalCode
            , @二级区划码      [ Base ] .RegionalCode
            , @当前区划码      [ Base ] .RegionalCode

     SET      @一级区划码 = @一级 + N ' 00 ' + N ' 00 '
     SET      @二级区划码 = @一级 + @二级 + N ' 00 '
     SET      @当前区划码 = @一级 + @二级 + @三级

     -- 分三种情形取全名

     IF @当前区划码 = @一级区划码
         SELECT @一级全名 = 名称
         FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
         WHERE 区划码 = @当前区划码

     ELSE
         IF @当前区划码 = @二级区划码
             BEGIN
                 SELECT @一级全名 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @一级区划码

                 SELECT @二级全名 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @当前区划码
             END
         ELSE
             BEGIN

                 SELECT @一级全名 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @一级区划码

                 SELECT @二级全名 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @二级区划码

                 SELECT @三级全名 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @当前区划码

             END

     SET @全名 =     @一级全名
                 + @二级全名
                 + @三级全名

     RETURN @全名

END

现在我们可以看看结果，在ManageMent 上运行：

USE [ RegionalCodeWorks ]
GO

SELECT *
, [ Action ] .全名( DEFAULT , 一级, 二级, 三级) AS 全名
FROM [ Action ] . [ 行政区情况 ] ( DEFAULT )

存在如下一些结果是需要重新处理这个函数的：

2 110100 市辖区 11 01 00 Current 20020331 北京市市辖区
3 110101 东城区 11 01 01 Current 20020331 北京市市辖区东城区

19 110200 县 11 02 00 Current 20020331 北京市县
20 110228 密云县 11 02 28 Current 20020331 北京市县密云县

以下我对二级名称重新作了处理：

凡二级名称是“市辖区”、“市”、“县”的，都不取。

USE [ RegionalCodeWorks ]
GO
/* ***** 对象:  UserDefinedFunction [Action].[全名]    脚本日期: 09/21/2006 18:38:36 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        LzmTW
-- Create date: 20060921
-- Description:    取行政区的全名
-- =============================================
CREATE FUNCTION [ Action ] . [ 全名 ]
(
      @当前时间      [ Base ] . [ RegionalDate ]      = N ' Current '
    , @一级          nchar ( 2 )
    , @二级          nchar ( 2 )
    , @三级          nchar ( 2 )
)
RETURNS nvarchar ( 200 )
AS
BEGIN
     -- 初始化变量

     DECLARE       @全名          nvarchar ( 200 )
            , @一级名称      nvarchar ( 100 )
            , @二级名称      nvarchar ( 100 )
            , @三级名称      nvarchar ( 100 )

     SET @一级名称 = N ''
     SET @二级名称 = N ''
     SET @三级名称 = N ''

     DECLARE       @一级区划码      [ Base ] .RegionalCode
            , @二级区划码      [ Base ] .RegionalCode
            , @当前区划码      [ Base ] .RegionalCode

     SET      @一级区划码 = @一级 + N ' 00 ' + N ' 00 '
     SET      @二级区划码 = @一级 + @二级 + N ' 00 '
     SET      @当前区划码 = @一级 + @二级 + @三级

     -- 分三种情形取全名

     IF @当前区划码 = @一级区划码
         SELECT @一级名称 = 名称
         FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
         WHERE 区划码 = @当前区划码

     ELSE
         IF @当前区划码 = @二级区划码
             BEGIN
                 SELECT @一级名称 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @一级区划码

                 SELECT @二级名称 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @当前区划码
             END
         ELSE
             BEGIN

                 SELECT @一级名称 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @一级区划码

                 SELECT @二级名称 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @二级区划码

                 SELECT @三级名称 = 名称
                 FROM [ Action ] . [ 行政区情况 ] ( @当前时间 )
                 WHERE 区划码 = @当前区划码

             END

     IF @二级名称 IN (N ' 市辖区 ' , N ' 市 ' , N ' 县 ' )
         SET @二级名称 = N ''

     SET @全名 =     @一级名称
                 + @二级名称
                 + @三级名称

     RETURN @全名

END

我将上面的X级全名改成了X级名称，这符实际。

最后，建一个存储过程，将可能需要用到的所有信息都输送出去：

USE [ RegionalCodeWorks ]
GO
/* ***** 对象:  StoredProcedure [Program].[行政区情况]    脚本日期: 09/21/2006 18:27:01 ***** */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:          LzmTW
-- Create date:    20060921
-- Description:   取行政区划的所有信息
-- =============================================
CREATE PROCEDURE [ Program ] . [ 行政区情况 ]
      @当前时间      [ Base ] . [ RegionalDate ]      = N ' Current '
AS
BEGIN
     SET NOCOUNT ON ;

     DECLARE @类型情况 TABLE
    (
         区划码ID     smallint
        ,类型ID         smallint
    )

     INSERT INTO @类型情况
     EXEC [ Action ] . [ 区划码类型情况 ] @当前时间

     SELECT      TOP 100 PERCENT
             d.区划码ID
            ,d.区划码
            ,d.名称
            , [ Action ] .全名( @当前时间 , d.一级, d.二级, d.三级) AS 全名
            ,a.类型
            ,b.级别
            ,d.一级
            ,d.二级
            ,d.三级
            ,d.截止日期
            ,d.起始日期
            ,a.类型ID
            ,b.级别ID
     FROM     Base.行政区类型 AS a
     INNER JOIN Base.行政区级别 AS b
     ON a.级别ID = b.级别ID
     INNER JOIN @类型情况 AS c
     ON a.类型ID = c.类型ID
     INNER JOIN [ Action ] .行政区情况( @当前时间 ) AS d
     ON d.区划码ID = c.区划码ID
     ORDER BY d.一级, d.二级, d.三级
END

这个数据库我觉得内容有意义，做为练习再好不过了。
若想得到更多的信息，比如历年数据对比，比如看看那些增加了换名了，看看城市化进程情况，也比如做做交叉表，等等，以后再练习。

附：行政区划数据数据库的设计(脚本)

下一文，再回到程序，实现简单的查询。

下一篇，行政区划程序的设计(一)