sql-server - 确定几个邮政编码是否连续

Question

我的客户有多个销售区域，其中每个销售区域都包含邮政编码列表。这些区域相当大，可以更容易地以如下格式存储:

区域由邮政编码范围从 00602 到 10012 以及 20020 到 30020 组成。

如何从邮政编码列表获取此类邮政编码范围的列表？

考虑以下数据

--This would be my list of all available zip codes in us:

CREATE TABLE [Zip](
    [Zip] [nvarchar](20) ,
    [State] [nvarchar](50) ,
)

--This would be the Sales Region List

CREATE TABLE [dbo].[SalesRegion](
    [AreaCode] [nvarchar](50) 
) 

--This would be the original large list Zip Codes for the SalesRegions

CREATE TABLE [dbo].[EnteredZip](
    [Zip] [nvarchar](20) ,
    [AreaCode] [nvarchar](50) 
) 

--This is where I would like to store the Zip Code Ranges

CREATE TABLE [dbo].[SearchableZip](
    [StartZip] [nvarchar](20) ,
    [EndZip] [nvarchar](20) ,
    [AreaCode] [nvarchar](50) 
) 

--Here is my sample Data:

--Some Zip Codes in US
insert into dbo.Zip (Zip,[State]) values ('00501'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00544'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00601'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00602'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00603'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00604'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00605'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00606'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00610'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00611'   ,'PR')
insert into dbo.Zip (Zip,[State]) values ('00612'   ,'PR')


--Some Sales Regions

Insert Into dbo.SalesRegion ( AreaCode ) values('Area1')
Insert Into dbo.SalesRegion ( AreaCode ) values('Area2')
Insert Into dbo.SalesRegion ( AreaCode ) values('Area3')


--The zip codes of the Sales Regions
insert Into EnteredZip (Zip,AreaCode) values ('00544' , 'Area1')
insert Into EnteredZip (Zip,AreaCode) values ('00601' , 'Area1')
insert Into EnteredZip (Zip,AreaCode) values ('00602' , 'Area1')

insert Into EnteredZip (Zip,AreaCode) values ('00604' , 'Area2')
insert Into EnteredZip (Zip,AreaCode) values ('00606' , 'Area2')

insert Into EnteredZip (Zip,AreaCode) values ('00501' , 'Area3')
insert Into EnteredZip (Zip,AreaCode) values ('00544' , 'Area3')
insert Into EnteredZip (Zip,AreaCode) values ('00601' , 'Area3')
insert Into EnteredZip (Zip,AreaCode) values ('00602' , 'Area3')
insert Into EnteredZip (Zip,AreaCode) values ('00603' , 'Area3')
insert Into EnteredZip (Zip,AreaCode) values ('00604' , 'Area3')

insert Into EnteredZip (Zip,AreaCode) values ('00610' , 'Area3')
insert Into EnteredZip (Zip,AreaCode) values ('00611' , 'Area3')
insert Into EnteredZip (Zip,AreaCode) values ('00612' , 'Area3')

将导致此条目出现在 SearchableZip 表中

AreaCode             StartZip             EndZip
-------------------- -------------------- -------------------------
Area1                00544                00602
Area2                00604                00604
Area2                00606                00606
Area3                00501                00604
Area3                00610                00612

是否可以使用 SQL 脚本创建 SearchableZip？

编辑

我修复了表声明和输出数据

Answer 1

是的，可以通过单个查询从列表中获取范围。为此，您将使用 CTE , ranking ，还有一点grey matter :

WITH ranked AS (
  SELECT
    Zip,
    AreaCode,
    ZipGroup = CAST(Zip AS int)
             - ROW_NUMBER() OVER (PARTITION BY AreaCode ORDER BY Zip)
  FROM EnteredZip
)
SELECT
  StartZip = MIN(Zip),
  EndZip   = MAX(Zip),
  AreaCode
FROM ranked
GROUP BY AreaCode, ZipGroup

输出:

StartZip             EndZip               AreaCode
-------------------- -------------------- -------------------------
00544                00544                Area1
00601                00602                Area1
00604                00604                Area2
00606                00606                Area2
00501                00501                Area3
00544                00544                Area3
00601                00604                Area3
00610                00612                Area3

此输出与您的输出不匹配，但与源数据匹配。

<小时/>

更新

如果 Zip 表是用于确定邮政编码列表连续性的引用表，则上述解决方案应修改如下:

WITH ZipRanked AS (
  SELECT
    Zip,
    State,
    ZipRank = ROW_NUMBER() OVER (PARTITION BY State ORDER BY Zip)
  FROM Zip
),
EnteredZipRanked AS (
  SELECT
    e.Zip,
    e.AreaCode,
    ZipGroup = z.ZipRank
             - ROW_NUMBER() OVER (PARTITION BY e.AreaCode ORDER BY e.Zip)
  FROM EnteredZip e
    INNER JOIN ZipRanked z ON e.Zip = z.Zip
)
SELECT
  StartZip = MIN(Zip),
  EndZip   = MAX(Zip),
  AreaCode
FROM EnteredZipRanked
GROUP BY AreaCode, ZipGroup