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

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

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

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