Author：水如烟
行政区划数据数据库的设计(五)
在上文中，实现了网上数据导入数据库。
不过上文中全部数据导入的过程，有一个环节是忽略了的，就是没有检查是不是原有的区划码全部有效，有没有中止“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:
-- Create date:
-- 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
下一篇数据库的最后一篇，取全名。
