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

Posted on 2006-09-20 23:39 水如烟(LzmTW) 阅读(466) 评论(0) 编辑收藏举报

在上文中，实现了网上数据导入数据库。

不过上文中全部数据导入的过程，有一个环节是忽略了的，就是没有检查是不是原有的区划码全部有效，有没有中止“Current”影射的。我是想当然的认为，后期版本肯定全部继承前期版本的。事实可能这样，但设计和操作逻辑不允许这样的想当然。当然，现在知道了，以后再补漏也不为迟。

这篇说附属表，关于行政区类型。

根据规约的说明，行政区分为三级，分别是省、省直辖市和地区州盟、市地辖区和县旗及省直辖县级市。

按我的理解，分别建两个表，一是行政区级别，二是行政区类型。

USE [RegionalCodeWorks]
GO
/****** 对象:  Table [Base].[行政区级别]    脚本日期: 09/20/2006 22:44:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Base].[行政区级别](
    [级别ID] [smallint] IDENTITY(0,1) NOT NULL,
    [级别] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK_行政区级别] PRIMARY KEY CLUSTERED
(
    [级别ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_行政区级别] UNIQUE NONCLUSTERED
(
    [级别] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

USE [RegionalCodeWorks]
GO
/****** 对象:  Table [Base].[行政区类型]    脚本日期: 09/20/2006 22:45:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Base].[行政区类型](
    [类型ID] [smallint] IDENTITY(0,1) NOT NULL,
    [类型] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [级别ID] [smallint] NOT NULL,
CONSTRAINT [PK_行政区类型] PRIMARY KEY CLUSTERED
(
    [类型ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_行政区类型] UNIQUE NONCLUSTERED
(
    [类型] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [Base].[行政区类型]  WITH CHECK ADD  CONSTRAINT [FK_行政区类型_行政区级别] FOREIGN KEY([级别ID])
REFERENCES [Base].[行政区级别] ([级别ID])
GO
ALTER TABLE [Base].[行政区类型] CHECK CONSTRAINT [FK_行政区类型_行政区级别]

至于哪个区划码应属哪种类型，在上图中已经标示了出来，本来觉得是固定的，只是对比数据后，发现并非如此。因此另建一个表，为行政区类型情况，归属于“稳定数据日期”类。

USE [RegionalCodeWorks]
GO
/****** 对象:  Table [Edit].[行政区类型情况]    脚本日期: 09/20/2006 22:46:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Edit].[行政区类型情况](
    [ID] [smallint] IDENTITY(1,1) NOT NULL,
    [类型ID] [smallint] NOT NULL,
    [范围] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NOT NULL,
    [截止日期] [Base].[RegionalDate] NOT NULL,
    [起始日期] [Base].[RegionalDate] NOT NULL,
    [备注] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_行政区类型情况] PRIMARY KEY CLUSTERED
(
    [类型ID] ASC,
    [截止日期] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Edit].[行政区类型情况].[截止日期]' , @futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[Base].[RegionalDateDefault]', @objname=N'[Edit].[行政区类型情况].[起始日期]' , @futureonly='futureonly'
GO
ALTER TABLE [Edit].[行政区类型情况]  WITH CHECK ADD  CONSTRAINT [FK_行政区类型情况_行政区类型] FOREIGN KEY([类型ID])
REFERENCES [Base].[行政区类型] ([类型ID])
GO
ALTER TABLE [Edit].[行政区类型情况] CHECK CONSTRAINT [FK_行政区类型情况_行政区类型]
GO
ALTER TABLE [Edit].[行政区类型情况]  WITH CHECK ADD  CONSTRAINT [CK_行政区类型情况] CHECK  (([截止日期]>=[起始日期]))
GO
ALTER TABLE [Edit].[行政区类型情况] CHECK CONSTRAINT [CK_行政区类型情况]

行政区级别和行政区类型属于基础表，建好表后就要初始化，增加数据。

考虑到图示给出的各类型范畴不一定全适用于所有数据，因此级别表中我加了“未知”项，同时类型表中也加了“未知”项。在查询数据时，如果有例外，它的类型显示为“未知”，这会提醒我们重新审查这些定义条件。

USE [RegionalCodeWorks]
GO

INSERT INTO [Base].[行政区级别]([级别]) VALUES (N'未知')
INSERT INTO [Base].[行政区级别]([级别]) VALUES (N'一级')
INSERT INTO [Base].[行政区级别]([级别]) VALUES (N'二级')
INSERT INTO [Base].[行政区级别]([级别]) VALUES (N'三级')

INSERT INTO [RegionalCodeWorks].[Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'未知' ,0)
INSERT INTO [RegionalCodeWorks].[Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'省' ,1)
INSERT INTO [RegionalCodeWorks].[Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'省直辖市' ,2)
INSERT INTO [RegionalCodeWorks].[Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'地区州盟' ,2)
INSERT INTO [RegionalCodeWorks].[Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'市地辖区' ,3)
INSERT INTO [RegionalCodeWorks].[Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'县旗' ,3)
INSERT INTO [RegionalCodeWorks].[Base].[行政区类型]([类型] ,[级别ID]) VALUES (N'省直辖县级市' ,3)

表[Edit].行政区类型情况的数据是,

USE [RegionalCodeWorks]
GO

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (1
           ,N'二级 = N''00'' AND 三级 = N''00'''
           ,N'20000101'
           ,N'省')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (2
           ,N'(二级 BETWEEN N''01'' AND N''20'' OR 二级 BETWEEN N''51'' AND N''99'' ) AND 三级 = N''00'' '
           ,N'20000101'
           ,N'省直辖市')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (3
           ,N'二级 BETWEEN N''21'' AND N''50'' AND 三级 = N''00'' '
           ,N'20000101'
           ,N'州盟')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (4
           ,N'三级 BETWEEN N''01'' AND N''20''  '
           ,N'20000101'
           ,N'市地辖区')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (5
           ,N'三级 BETWEEN N''21'' AND N''80'' '
           ,N'20000101'
           ,N'县旗')

INSERT INTO [Edit].[行政区类型情况]
           ([类型ID]
           ,[范围]
           ,[起始日期]
           ,[备注])
     VALUES
           (6
           ,N'三级 BETWEEN N''81'' AND N''99'' '
           ,N'20000101'
           ,N'省直辖县级市')

如果自己理解错了,可以随时改正。

现在定义两个函数和一个存储（感冒了，话不多说也是好事吧）

USE [RegionalCodeWorks]
GO
/****** 对象:  UserDefinedFunction [FUNCTION].[行政区类型情况]    脚本日期: 09/20/2006 23:08:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [FUNCTION].[行政区类型情况]
(
     @当前时间    [Base].[RegionalDate]    = N'Current'
)
RETURNS TABLE
AS
RETURN
(
    SELECT a.*
    FROM [Edit].[行政区类型情况] a
    RIGHT OUTER JOIN
    (
        SELECT    [类型ID]
                ,MIN(DISTINCT [截止日期])AS 查询时间
        FROM    [Edit].[行政区类型情况]
        WHERE   @当前时间 BETWEEN [起始日期] AND [截止日期]
        GROUP BY[类型ID]
    ) b
    ON a.类型ID = b.类型ID AND a.截止日期 = b.查询时间
)

USE [RegionalCodeWorks]
GO
/****** 对象:  UserDefinedFunction [Action].[行政区情况]    脚本日期: 09/20/2006 23:10:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [Action].[行政区情况]
(
     @当前时间    [Base].[RegionalDate]    = N'Current'
)
RETURNS TABLE
AS
RETURN
(
    SELECT     a.区划码ID
            ,a.区划码
            ,b.名称
            ,a.一级
            ,a.二级
            ,a.三级
            ,b.截止日期
            ,b.起始日期
    FROM [Base].[区划码] a
    INNER JOIN [FUNCTION].[区划码情况](@当前时间) b
    ON a.区划码ID = b.区划码ID
)

存储过程，取区划码对应的类型ID.

USE [RegionalCodeWorks]
GO
/****** 对象:  StoredProcedure [Action].[区划码类型情况]    脚本日期: 09/20/2006 23:11:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        LzmTW
-- Create date: 20060920
-- Description:    取区划码的类型ID
-- =============================================
CREATE PROCEDURE [Action].[区划码类型情况]
     @当前时间    [Base].[RegionalDate]    = N'Current'
AS
BEGIN
    --通过临时表来处理数据

    IF EXISTS
    (
        SELECT 1 FROM tempdb.dbo.sysobjects
        WHERE [name] =  N'##区划码类型情况'  AND xtype=N'U'
    )
        DROP TABLE ##区划码类型情况

    CREATE TABLE ##区划码类型情况
        (
             区划码ID    smallint
            ,类型ID    smallint
        )

    --取类型定义为未知的ID
    DECLARE @未知ID smallint

    SELECT    @未知ID = 类型ID
    FROM    [Base].行政区类型
    WHERE    类型 = N'未知'

    --所有区划码的类型初始化为未知
    INSERT INTO ##区划码类型情况
    SELECT   区划码ID
            ,@未知ID as 类型ID
    FROM [FUNCTION].区划码情况(@当前时间)

    --以下处理的正确性取决于类型定义不存在交集!!
    DECLARE  @Building    nvarchar(MAX)
            ,@SQL        nvarchar(MAX)
            ,@类型ID    smallint
            ,@范围        nvarchar(MAX)

    SET @Building = N'
        UPDATE ##区划码类型情况
        SET 类型ID = b.类型ID
        FROM ##区划码类型情况 a
        INNER JOIN
        (
            SELECT   区划码ID
                    ,{类型ID} AS 类型ID
            FROM    [Action].[行政区情况](''' + @当前时间 + N''')
            WHERE    {WHERE}
        ) b
        ON a.区划码ID = b.区划码ID '

    --遍历类型定义表更新区划码类型
    DECLARE 行政区类型_Cursor CURSOR FOR
    SELECT   类型ID
            ,范围
    FROM  [FUNCTION].行政区类型情况(@当前时间)


    OPEN 行政区类型_Cursor

    FETCH NEXT
    FROM 行政区类型_Cursor
    INTO @类型ID, @范围

    WHILE (@@fetch_status<>-1)
    BEGIN

        IF (@@fetch_status<>-2)
        BEGIN

            SET @SQL = REPLACE(@Building, N'{类型ID}', @类型ID)
            SET @SQL = REPLACE(@SQL, N'{WHERE}', @范围)

            EXEC (@SQL)

            FETCH NEXT
            FROM 行政区类型_Cursor
            INTO @类型ID, @范围
        END

    END

    CLOSE 行政区类型_Cursor
    DEALLOCATE 行政区类型_Cursor

    SELECT *
    FROM ##区划码类型情况
END

最后建个存储过程测试一下：

USE [RegionalCodeWorks]
GO
/****** 对象:  StoredProcedure [Program].[Test类型情况]    脚本日期: 09/20/2006 23:12:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [Program].[Test类型情况]
     @当前时间    [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.名称
            ,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

下一篇数据库的最后一篇，取全名。

刷新页面返回顶部

水如烟

导航

公告

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