寻求分析处理不规则数据的最佳办法，详情见内，班竹及各位高手务请帮忙

[spring_ok]

写一个存贮过程在20秒钟内完成对一个包含46000条记录的表的分析和更新，可能性有多大？

英文数据，有两个表，一个存的是不规则的地址（可能有一到三个地址，有的用逗号分开城市、州名和邮政编码，有的用空格分开，有的城市名是一个单词，有的是两个或三个单词，大部分的州名用两位字符的缩写，但有的是用全称，有的地址还不包含州名。）。另一个存的是邮政编码，城市，州名。NRA_Corp 表开始时只有 PAddressFUll字段有值，现在要将不规则的地址解析，分解成地址一，地址二，地址三，城市，州名，邮政编码。解析正确的将其Success字段更新为1，处理时要求查邮政编码表中是否存在对应的邮政编码和城市名，存在才更新。要求用存贮过程来完成

我做了一个存贮过程，使用游标来处理这些地址，处理46000条记录的执行时间大约需要6－8分钟（PIII 733〔。客户说不采用游标，他可以在20秒内完成对这些数据的分析处理。这让我有些无法接受。请各位高手看看有没有更好的办法来完成这个任务。究竟有没有可能在20秒内（使用P4 1.8G, 1G RAM) 完成这个工作？

表结构如下：

CREATE TABLE [dbo].[NRA_CORP] (
[PAddressFull] [varchar] (400) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PAddress1] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAddress2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAddress3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PCity] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PState] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PZip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Success] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NRA_ZipCode] (
[ZIPCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ZIPCodeType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CityType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AreaCode] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Latitude] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Longitude] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NRA_CORP] WITH NOCHECK ADD
CONSTRAINT [PK_NRA_CORP] PRIMARY KEY CLUSTERED
(
[PAddressFull]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[NRA_ZipCode] WITH NOCHECK ADD
CONSTRAINT [PK_NRA_ZipCode] PRIMARY KEY CLUSTERED
(
[ZIPCode],
[City]
) ON [PRIMARY]
GO


部分数据见下一回贴

[liuyun2003]

如果一定要循环，可以使用WHILE来代替游标，那个游标有的时候很慢的。我想应该有办法在20S内完成的。

[spring_ok]

我主要的考虑是：这里需要取出可能的城市名称（一个单词，两个单词甚至三个单词）与NRA_ZipCode表去做JOIN来确定是否是一个正确的邮政编码和城市。只有邮政编码和城市正确的情况下，才更新数据。在这种情况下，岂不是要JOIN两三次？

另外你这个例子，我看得不是很明白，可以说明一下它的用途吗？

[spring_ok]

可以给一个在T-SQL中用While 代替游标做循环的例子吗？

[pengdali]

参考：
update 表1 set 列1=表2.列1 where 表1.编号=表2.编号 and 表1.邮政编码 正确 and ...

[pengdali]

得到用','分割的子串：

create function getstrofindex (@str varchar(8000),@index int =0)
returns varchar(8000)
as
begin
declare @str_return varchar(8000)
declare @start int
declare @next int
declare @location int
select @start =1
select @next =1
select @location = charindex(',',@str,@start)
while (@location <>0 and @index > @next )
begin
select @start = @location +1
select @location = charindex(',',@str,@start)
select @next =@next +1
end
if @location =0 select @location =len(@str)+1
select @str_return = substring(@str,@start,@location -@start)
if (@index <> @next ) select @str_return = ''
return @str_return
end
go
select dbo.getstrofindex('aa vv,cc ee, sdfasdfasf',3)

[spring_ok]

多谢班竹及各位高手帮忙。
现在的情况是，除了用逗号分隔之外，也有可能仅仅是用空格分开的。这种情况就比较复杂。
请再帮忙提提意见。

希望找到最好的办法。

[samuelpan]

将过程分解成多步骤处理，然后用临时表代替游标。

贴出你的存储过程大家看看。

[spring_ok]

这里是我的需要用六七分钟才能完成的存贮过程：

/*************************************************************************
Store procedure to parse address data
Created by: Spring Zhang
Created on: 05/28/2003
*************************************************************************/
Create Procedure [ParsingPAddress_Bak]
AS

set nocount on

Declare @PAddressFull varchar(400), @PAddress1 varchar(150), @PAddress2 varchar(100), @PAddress3 varchar(100)
Declare @PCity varchar(50), @PState varchar(50), @PZip varchar(50)
Declare @RAddressFull varchar(400), @RAddress1 varchar(150), @RAddress2 varchar(100), @RAddress3 varchar(100)
Declare @RCity varchar(50), @RState varchar(50), @RZip varchar(50), @RCity1 varchar(50), @RCity2 varchar(50)
Declare @RAddressFull1 varchar(400), @RAddressFull2 varchar(400), @PAddressTemp varchar(400)

declare @iCount int, @iPos1 int, @iPos2 int

Declare addr_Cursor Cursor for
select PAddressFull, Reverse(paddressfull) as RAddressFull,
substring(Reverse(paddressfull),0, CharIndex(' ', Reverse(paddressfull), 0)) as RZip
from NRA_CORP
where Reverse(paddressfull) like '[0-9][0-9][0-9][0-9][0-9]%' or
Reverse(paddressfull) like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9]%'
for Update

Open addr_Cursor

FETCH NEXT FROM addr_Cursor
INTO @PAddressFull, @RAddressFull, @RZip

WHILE @@FETCH_STATUS = 0
begin
select @RAddress1='', @RAddress2='', @RAddress3='', @RCity='', @RState=''
select @PAddress1='', @PAddress2='', @PAddress3='', @PCity='', @PState='', @PZip='', @PAddressTemp=' '

--print '@PaddressFull='+@PAddressFull
--print '@RaddressFull='+@RAddressFull
--print '@RZip=' + @RZip

set @iPos1 = len(@RZip)

--cut off the zipcode part
set @RAddressFull = ltrim(rtrim(substring(@RAddressFull, @iPos1+1, Len(@RAddressFull)-@iPos1)))
--print '@RaddressFull='+@RAddressFull

--Find the State (in case of state is not provided, it is the City)
set @iPos2 = CharIndex(' ', @RAddressFull, 0)

set @RState = substring(@RAddressFull, 0, @iPos2)
--print '@RState=' + @Rstate

set @iPos1 = len(@RState)

--cut off the State part
set @RAddressFull = ltrim(rtrim(substring(@RAddressFull, @iPos1+1, Len(@RAddressFull)-@iPos1)))
--print '@RaddressFull='+@RAddressFull

--Find the City
if len(@RState) <> 2 --there is no state provided, current should be the city
begin
set @RAddressFull1 = @RAddressFull
set @RCity1=@RState
set @iPos2 = CharIndex(' ', @RAddressFull, 0)
set @RCity2 = substring(@RAddressFull, 0, @iPos2)
set @iPos1 = len(@RCity2)
set @RCity2 = @RCity1 + ' ' + @RCity2
set @RAddressFull2 = ltrim(rtrim(substring(@RAddressFull, @iPos1+1, Len(@RAddressFull)-@iPos1)))
set @RState = ''

end
else
begin
set @iPos2 = CharIndex(' ', @RAddressFull, 0)
set @RCity1 = substring(@RAddressFull, 0, @iPos2)
set @iPos1 = len(@RCity1)
set @RAddressFull = ltrim(rtrim(substring(@RAddressFull, @iPos1+1, Len(@RAddressFull)-@iPos1)))
set @RAddressFull1 = @RAddressFull

set @iPos2 = CharIndex(' ', @RAddressFull, 0)
set @RCity2 = substring(@RAddressFull, 0, @iPos2)
set @iPos1 = len(@RCity2)
set @RCity2 = @RCity1 + ' ' + @RCity2
set @RAddressFull2 = ltrim(rtrim(substring(@RAddressFull, @iPos1+1, Len(@RAddressFull)-@iPos1)))
end

set @RCity1 = replace(@RCity1, ',', '')
set @RCity2 = replace(@RCity2, ',', '')
if (SubString(@RAddressFull1, 1, 1) = ',')
begin
set @RAddressFull1 = substring(@RAddressFull1, 2, len(@RAddressFull1)-1)
end

if (SubString(@RAddressFull2, 1, 1) = ',')
begin
set @RAddressFull2 = substring(@RAddressFull2, 2, len(@RAddressFull2)-1)
end

--print '@RCity1=' + @RCity1
--print '@RCity2=' + @RCity2
--print '@RAddressFull1=' + @RAddressFull1
--print '@RAddressFull2=' + @RAddressFull2
--print '--------------------------------------------------------------------'
--print ''


if Len(@RZip)>5
begin
set @RZip = substring(@RZip, 6, 5)
end

set @PZip = reverse(@RZip)

--Find the actual city and state name from the NRA_ZipCode table
Select @PCity = LTRIM(RTrim(City)), @PState = LTrim(RTrim(StateCode)) from NRA_ZipCode
where ZipCode=@PZip and (City = reverse(@RCity1) or City = reverse(@RCity2) )

if @PCity <> '' and @PState <> ''
begin
--print 'data found ' + @PCity
if @PCity = reverse(@RCity1)
begin
set @PAddressTemp = reverse(@RAddressFull1)
end

if @PCity = reverse(@RCity2)
begin
set @PAddressTemp = reverse(@RAddressFull2)
end


--print '@PAddressTemp=' + @PAddressTemp

--check to see if need to split the address
set @iPos1 = CharIndex(',', @PAddressTemp, 0)
if @iPos1 > 0
begin
set @PAddress1 = substring(@PAddressTemp, 0, @iPos1)
set @PAddressTemp = ltrim(rtrim(substring(@PAddressTemp, @iPos1+1, Len(@PAddressTemp)-@iPos1)))

--check to see if need to split the address again
set @iPos1 = CharIndex(',', @PAddressTemp, 0)
if @iPos1 > 0
begin
set @PAddress2 = substring(@PAddressTemp, 0, @iPos1)
set @PAddress3 = ltrim(rtrim(substring(@PAddressTemp, @iPos1+1, Len(@PAddressTemp)-@iPos1)))
end
else
begin
Set @PAddress2 = @PAddressTemp
Set @PAddress3 = null
end

end
else
begin
Set @PAddress1 = @PAddressTemp
Set @PAddress2 = null
Set @PAddress3 = null
end

--print 'Update NRA_CORP Set PAddress1='''+@PAddress1+''', PAddress2='''+@PAddress2+''', PAddress3='''+@PAddress3+''', PCity='''+ @PCity + ''', PState=''' + @PState + ''', PZip=''' + @PZip + ''', Success=1 Where Current of addr_Cursor'

Update NRA_CORP Set PAddress1=@PAddress1, PAddress2=@PAddress2, PAddress3=@PAddress3,
PCity=@PCity, PState=@PState, PZip=@PZip, Success=1
Where Current of addr_Cursor

end

FETCH NEXT FROM addr_Cursor
INTO @PAddressFull, @RAddressFull, @RZip
end

CLOSE addr_Cursor
DEALLOCATE addr_Cursor
GO

[spring_ok]

到最后发现整了一个乌龙！

我上面这个存贮过程，不做任何改动，放在P4 2。0 ， 256M DDR RAM的机器上运行，也只需要29秒的时间。

我做了一点优化之后只需要21秒就完成了。

所以这个性能问题就不再重要了。 我还是认为在这种情况下用游标是最合适的方法。

还有，不知为什么 P4 和P3 有如此大的差别？

[angel_lee]

up for you

[spring_ok]

部分数据如下：
NRA_Corp表 PAddress:
3200 CARLISLE BLVD, STE 226 ALBUQUERQUE NEW MEXICO 87110
8801 SOUTH YALE STE 310 TULSA OK 74137
1155 LEGATO LAS VEGAS NV 89119
1017 HARVARD ST MANAGER SANTA MONICA CA 90403
8990 MANALANG ROAD LAS VEGAS NV 89123
474 HILLEDGE DR LAGUNA BEACH CA 92651
1538 MARCELINA AVE #3 TORRANCE CA 90501
21820 VIA REGINA MANAGER SARATOGA CA 95070
1775 BROADWAY 23 FL NEW YORK NY 10019
24 ROBINSON RD MEMBER CLINTON NY 13323
535 E DIEHL RD STE 100 NAPERVILLE IL 60563
5600 3 FIRST NATIONAL PLAZA CHICAGO IL 60602
660 WHILE PLAINS ROAD TARRYTOWN NY 10591-5193
5935 CHAMBERRY CIRCLE RENO NV 89511
580 CALIFORNIA ST. 5TH FL SAN FRANCISCO CA 94104
P.O. BOX 541057 DALLAS TX 75354-1057
135 MAIN STREET SAN FRANSICO CA 94105
1375 NE 123RD STREET, MIAMI, FL 33161
2201 WILSON BLVD., STE. 500, ARLINGTON, VA 22201
5855 OAKRIDGE RD MEMBER HAMILTON OH 45011
6200 S. QUEBEC STREET, GREENWOOD VILLAGE, CO 80111
225 BYERS RD., MIAMISBURG, OH 45342
3500 HIGHWAY 190, MANDEVILLE, LA 70471-3124
3140 WYOMING, NE J117 ALBUQUERQUE NEW MEXICO 87109
4415 GOVERNEMENT BLVD., MOBILE, AL 36693
5301 LEGACY DR., PLANO, TX 75024
6003 VETERANS PKWY., COLUMBUS, GA 31902
2432 GRAND CONCOURSE, BRONX, NY 10458
ONE SOUTH WACKER DRIVE, CHICAGO, IL 60606
708 BLAIR MILL RD., WILLOW GROVE, PA 19090
PERIMETER PLACE ONE, 518 OLD KENTUCKY ROAD, COOKEVILLE, TN 38502
1414 CORN PRODUCTS ROAD, CORPUS CHRISTI, TX 78409
484 SW BORLAND AVE. MANAGER/MEMBER WEST LINN OR 97068
8632 WOODBEAR DR SARASOTA FL 34238
TWO NORTHPARK EAST, #800, DALLAS, TX 75231
2 N RIVERSIDE PLAZA CHICAGO IL 60606
1020 MACON FT WORTH TX 76102
8 MANOR DRIVE MONTICELLO NY 12701
THREE TYCO PARK EXECTER NH 03833
820 FIFTH AVE NEW YORK NY 10021
502 EAST JOHN STREET C/O CSC SVCS OF NV CARSON CITY NV 89706
754 SOUTH COUNTY ROAD MANAGER/MEMBER PALM BEACH FL 33480
115 W 30TH ST 1104 NEW YORK NY 10001
1500 PRODELIN DRIVE NEWTON NC 28658
1 WILLIAMS CENTER TULSA OK 74172
1027 S CARSON CARSON CITY NV 89701
6795 E TENNESSEE AVE #360 MANAGER DENVER CO 80224
18300 VON KARMEN AVE STE 440 IRVINE CA 92612
860 RIDGE LAKE BLVD MEMBER MEMPHIS TN 38120
400 MORGAN STATION RD WOODBINE MD 21797
850 KALISTE SALOOM RD STE 219 LAFAYETTE LA 70508
6399 JIMMY CARTER BLVD NORCROSS GA 30071
767 FIFTH AVE., NEW YORK, NY 10153
3411 WEST COUNTY RD. HOBBS NEW MEXICO 88240
18200 NE UNION HILL ROAD, SUITE 110 MANAGER REDMOND WA 98052
TWO LIBERTY PLACE, 1601 CHESTNUT STREET, PHILADELPHIA, PA 19103
2900 CAREW TOWER, 441 VIN ST., CINCINNATI, OH 45202
225 SUMMIT AVE., MONTVALE, NJ 07645
104 EAST 40TH STREET, ROOM 903, NEW YORK, NY 10016
1200 CENTRE PARK BLVD., DESOTO, TX 75115
1701 GOLF RD., SUITE 1200, ROLLING MEADOWS, IL 60008
295 NORTH MAPLE AVE., BASKING RIDGE, NJ 07920
3 ADA, IRVINE, CA 92618
4 GATEHALL DRIVE, STE. 110, PARSIPPANY, NJ 07054
2701 24TH AV PO BOX 1510 GULFPORT MS 39502-1510
225 W. WACKER DR. STE. 400 CHICAGO IL 60606
536 CEDAR PARK CIR LAVERGNE TN 37086
P.O. BOX 2009 BELLEVUE WA 98009
2811 HAYES RD HOUSTON TX 77082
1901 VAN DYKE ROAD, PLAINFIELD IL, 60544
229 S. STATE ST., DOVER DE (20, 000 PREF $
685 SPRING STREET PMB 112 FRIDAY HARBOR WA 98250
1227 VALLEY ROAD P.O. BOX 38 MERTZTOWN PA 19539-0038
14 GROVEPARK STREET RICHMOND HILLS,ONTARIO CN L4E-3L44
100 PEARL 16 FLOOR HARTFORD CT 06103-4506
601 W 1ST AVE SPOKANE WA 99201-5015
P.O. 57652 SHERMAN OAKS CA 91423
5000 WINDPLAY DR EL DORADO CA 95762
6200 S QUEBEC ST MANAGER GREENWOOD VILLAGE CO 80111-4729
250 PARK CENTER BLVD BOISE ID 83726
6323 W. VAN BUREN PHOENIX AZ 85043
325 S. 3RD ST. #1247 LAS VEGAS NV 89101
204 BEALL LANE DAPHNE AL 36526
370 LANTANA ST CORPUS CHRISTI TX 78404
4110 N SCOTTSDALE RD STE 308 SCOTTSDALE AZ 85251
2100 RESTON PKWY STE 500 RESTON VA 20191
115 VIA PICO PLAZA STE 205 SAN CLEMENTE CA 92672
WEST LOOP SOUTH, STE 210 HOUSTON TX 77401
55 GLENLAKE PKWY NE ASST TREAS/SECT ATLANTA GA 30328
1325 NORTH MEADOW PKWY #110 ROSWELL GA 30076
94 GRANDVIEW BLVD READING PA 19609
3228 CHANNEL 8 DRIVE MEMBER LAS VEGAS NV 89109
539 SOUTH MAIN STREET FINDLAY OH 45840
2300 N HIGHWAY #121 MANAGER EULESS TX 76039
9 RIVERSIDE RD WESTON MA 02493
7185 SETTLERS RIDGE ROAD GATES MILLS OH 44040
2950 E ADVANCE LN COLMAR PA 18915
51 W ELLIOT RD STE 101 TEMPE AZ 85284
5615 HIGH POINT DR IRVING TX 75038
10825 E. 47TH AVE., DENVER, CO 80239
1717 DEERFIELD ROAD DEERFIELD IL 60015
2078 PROSPECTER AVE PARK CITY UT 84060
C/0 U.S. CORP CO., 306 SO. STATE ST., DOVER DE (2, 000 SH COM $25.00)
11330 BRITTMOORE PARK DR., HOUSTON, TX 77041
ONE NEW YORK PLAZA, 40TH FLR., NEW YORK, NY 10004
5450 NORTH CUMBERLAND AVE., CHICAGO, IL 60656
1594 SARA RD. RIO RANCHO NEW MEXICO 87124
11701 KINARD, NORTH LITTLE ROCK, AR 72117
4004 CARLISLE NE STE B ALBUQUERQUE NEW MEXICO 87107
5335 TRIAGLE PKWY. STE, 550-A, NORCROSS, GA 30092
900 COUNTRY CLUB DR NW RIO RANCHO NEW MEXICO 87124
2700 N.E. ANDRESEN, VANCOUVER, WA 98661
1105 SEWELL LANE, RYDAL, PA 19046
123 ROBERT S. KERR AVE. (MT-806), OKLAHOMA CITY, OK 73102
COLLEGE OF ARTS & SCIENCES LSU, BATON ROUGE, LA 70803
908 BURNETT STREET, WICHITA FALLS, TX 76301
860 CANAL STREET, STAMFORD, CT 06902
1206 DECATUR ST., NEW ORLEANS, LA 70116
3812-E TARHEEL DR., RALEIGH, NC 27609
6100 NORTH WESTERN AVE., OKLAHOMA CITY, OK 73118
115 EAST SOUTH STREET GALESBURG IL 61401
822 7TH STREET STE 202 GREELEY CO 80631-9663
8515 E. ORCHARD ROAD, GREENWOOD VILLAGE, CO 80111
10200 SUNSET DRIVE MANAGER MIAMI FL 33173
11835 NE GLENN WIDING DR #E PORTLAND OR 97220
PO BOX 2183 SANTA MONICA CA 90407-1283
295 N MAPLE AVE BASKING RIDGE NJ 07920
4385 S JUMBO CIRCLE CARSON CITY NV 89704
999 YAMATO #100 BOCA RATON FL 33431
3765 ARDEN WAY WELLINGTON NV 89444
2509 WARWICK OKLAHOMA CITY OK 73116
2515 DRANEFIELD ROAD, LAKELAND, FL 33811
10038 BODE ST., PLAINFIELD, IL 60544
PO BOX 2602 2341 EASTLAKE AVE E MEMBER SEATTLE WA 98111
4000 HOLLYWOOD BLVD, SUITE 500-N HOLLYWOOD FL 33021
ONE HARRAH'S COURT MANAGER/MEMBER LAS VEGAS NV 89119
P O BOX 8100 MONTREAL, QUEBEC CN H3C-3N4
6443 E IONA RD IDAHO FALLS ID 83401
1055 CORPORATE CENTER DR POB 389 OCONOMOWOL WI 53066
10002 SW FREEWAY HOUSTON TX 77075
11755 WILSHIRE BLVD W LOS ANGELES CA 90025
P.O. BOX 591 BRADY TX 76825
3000 EXECUTIVE PKWY., STE. 515, SAN RAMON, CA 94583
2402 DANIELS ST., MADISON, WI 53718
6125 PRESERVATION DRIVE, CHATTANOOGA, TN 37416
75 KITTS LANE, NEWINGTON, CT 06111
2000 CORPORATE SQ. BLVD.#101, JACKSONVILLE, FL 32216
1105 NORTH MARKET STREET, WILMINGTON, DE 19899
1301 CAPITAL OF TEXAS HWY., C-300, AUSTIN, TX 78746
1627 COLE BLVD., GOLDEN, CO 80401
7100 EAGLE CREST BLVD., STE. B, EVANSVILLE, TN 47714
4591 SAN MATEO NE ALBUQUERQUE NEW MEXICO 87109-2010
71 E. WATER STREET, CHILLICOTHE, OH 45601-2577
277 PARK AVE., NEW YORK, NY 10172
800 CONNECTICUT AVE., NORWALK, CT 06856
777 N. CAPITOL ST., NE STE 600, WASHINGTON, DC 20002-4240
2775 SANDERS RD., NORTHBROOK, IL 60062
W. MT. PLEASANT RD., ZACHARY, LA 70791
670 RIVERSIDE DRIVE, MEMPHIS, TN 38103
JOURNAL CENTER ALBUQUERQUE NEW MEXICO 87100
1009 HIGHVIEW ROAD EAST PEORIA IL 61611
8101 E PRENTICE AVE STE. 400 ENGLEWOOD CO 80111
2735 E. PARLEYS WAY, SUITE 301, SALT LAKE CITY, UT 84109
1 HORACE MANN PLAZA SPRINGFIELD, IL
707 3RD STREET, 2ND FLOOR
170 WEST PACES FERRY MANAGER ATLANTA GA 30305
52 NICK RD MIDDLEBURY CT 06762
NEW MEXICO 87102
217 WEST MANHATTAN AVE SANTA FE NEW MEXICO 87501
1924 SOUTH UTICA SUITE #1018 TULSA OK 74104
630 S KENMORE #514 LOS ANGELES CA 90005
2999 OAK RD 7TH FLR WALNUT CREEK CA 94597
725 GROVE STREET SAN FRANCSCO CA 94102
702 SW 8TH STREET MEMBER BENTONVILLE AR 72716
3292 SKYVIEW RIDGE WY CHINO HILLS CA 91709
6 BRIGHTON RD CLIFTON NJ 07015
STE 3605-1199 MARINASIDE CRES. VANCOUVER BC V6Z-2Y2
8247 HASCALL MANAGER/MEMBER OMAHA NE 68124

[spring_ok]

NRA_ZipCode 表:
ZipCode, City, State, StateCode
00501 Holtsville New York NY
00544 Holtsville New York NY
00601 Adjuntas Puerto Rico PR
00601 Jard de Adjuntas Puerto Rico PR
00601 Urb San Joaquin Puerto Rico PR
00602 Aguada Puerto Rico PR
00602 Alts de Aguada Puerto Rico PR
00602 Comunidad Las Flores Puerto Rico PR
00602 Ext Los Robles Puerto Rico PR
00602 Parc Palmar Novoa Puerto Rico PR
00602 Urb Brisas del Mar Puerto Rico PR
00602 Urb Isabel la Catolica Puerto Rico PR
00602 Urb Montemar Puerto Rico PR
00602 Urb San Cristobal Puerto Rico PR
00603 Aguadilla Puerto Rico PR
00603 Bda Caban Puerto Rico PR
00603 Bda Esteves Puerto Rico PR
00603 Bda Nueva Puerto Rico PR
00603 Bo Borinquen Puerto Rico PR
00603 Comunidad Borinquen Puerto Rico PR
00603 Comunidad Corrales Puerto Rico PR
00603 Ext El Prado Puerto Rico PR
00603 Ext Marbella Puerto Rico PR
00603 Jard de Anasco Puerto Rico PR
00603 Jard de Borinquen Puerto Rico PR
00603 Jard Maribel Puerto Rico PR
00603 Paseo Universitario Puerto Rico PR
00603 Ramey Puerto Rico PR
00603 Repto Jimenez Puerto Rico PR
00603 Repto Juan Aguiar Puerto Rico PR
00603 Repto Lopez Puerto Rico PR
00603 Repto Tres Palmas Puerto Rico PR
00603 Sect Las Villas Puerto Rico PR
00603 Urb Borinquen Puerto Rico PR
00603 Urb Cristal Puerto Rico PR
00603 Urb El Prado Puerto Rico PR
00603 Urb Esteves Puerto Rico PR
00603 Urb Garcia Puerto Rico PR
00603 Urb Industrial Montana Puerto Rico PR
00603 Urb Las Americas Puerto Rico PR
00603 Urb Maleza Gdns Puerto Rico PR
00603 Urb Marbella Puerto Rico PR
00603 Urb Retiro Puerto Rico PR
00603 Urb Rubianes Puerto Rico PR
00603 Urb San Carlos Puerto Rico PR
00603 Urb Santa Elena Puerto Rico PR
00603 Urb Santa Maria Puerto Rico PR
00603 Urb Santa Marta Puerto Rico PR
00603 Urb Victoria Puerto Rico PR
00603 Urb Vista Alegre Puerto Rico PR
00603 Urb Vista Verde Puerto Rico PR
00603 Valle Universitario Puerto Rico PR
00603 Villa Alegria Puerto Rico PR
00603 Villa Linda Puerto Rico PR
00603 Villa Lydia Puerto Rico PR
00603 Villa Universitaria Puerto Rico PR
00603 Villas de Almeria Puerto Rico PR
00604 Aguadilla Puerto Rico PR
00604 Ramey Puerto Rico PR
00604 Sect Las Villas Puerto Rico PR
00605 Aguadilla Puerto Rico PR
00606 Maricao Puerto Rico PR
00606 Urb San Juan Bautista Puerto Rico PR
00610 Anasco Puerto Rico PR
00610 Colinas de Librada Puerto Rico PR
00610 Jard de Anasco Puerto Rico PR
00610 Paseo del Valle Puerto Rico PR
00610 Repto Daguey Puerto Rico PR
00610 Urb Brisas de Anasco Puerto Rico PR
00610 Urb Flamboyanes Puerto Rico PR
00610 Urb Los Maestros Puerto Rico PR
00610 Urb Nieto Puerto Rico PR
00610 Urb Sagrado Corazon Puerto Rico PR
00610 Urb San Antonio Puerto Rico PR
00611 Angeles Puerto Rico PR
00612 Alt de Juncos Puerto Rico PR
00612 Alt de San Felipe Puerto Rico PR
00612 Arecibo Puerto Rico PR
00612 Bda Duhamel Puerto Rico PR
00612 Bo Dominguito Puerto Rico PR
00612 Bo El Pasaje Puerto Rico PR
00612 Bo Esperanza Puerto Rico PR
00612 Bo Hato Arriba Puerto Rico PR
00612 Bo Islote Ii Puerto Rico PR
00612 Bo Jarealitos Puerto Rico PR
00612 Bo Obrero Puerto Rico PR
00612 Bo Santana Puerto Rico PR
00612 Comunidad Buenos Aires Puerto Rico PR
00612 Comunidad Islote Puerto Rico PR
00612 Est de Arecibo Puerto Rico PR
00612 Est de Balseiro Puerto Rico PR
00612 Ext Las Brisas Puerto Rico PR
00612 Ext Marisol Puerto Rico PR
00612 Ext Tanama Puerto Rico PR
00612 Ext Villa los Santos I Puerto Rico PR
00612 Ext Villa los Santos Ii Puerto Rico PR
00612 Factor Puerto Rico PR
00612 Jard de Arecibo Puerto Rico PR
00612 Jard de San Rafael Puerto Rico PR
00612 La Herradura Puerto Rico PR
00612 La Mucura Puerto Rico PR
00612 Parc Mattey Puerto Rico PR
00612 Parc Navas Puerto Rico PR
00612 Parc Perez Puerto Rico PR
00612 Parc Rodriguez Olmo Puerto Rico PR
00612 Parq de Jardines Puerto Rico PR
00612 Paseos Reales Puerto Rico PR
00612 Repto Marquez Puerto Rico PR
00612 Repto Martell Puerto Rico PR
00612 Repto San Jose Puerto Rico PR
00612 Repto San Juan Puerto Rico PR
00612 Sect Cuchil Puerto Rico PR
00612 Sect El Cano Puerto Rico PR
00612 Sect Jarealitos Puerto Rico PR
00612 Sect Las Animas Puerto Rico PR
00612 Sect Los Gallegos Puerto Rico PR
00612 Sect Los Llanos Puerto Rico PR
00612 Sect Los Mora Puerto Rico PR
00612 Sect Los Rivera Puerto Rico PR
00612 Urb Animas Puerto Rico PR
00612 Urb Arecibo Gdns Puerto Rico PR
00612 Urb Bo Obrero Puerto Rico PR
00612 Urb Camino del Valle Puerto Rico PR
00612 Urb College Park Puerto Rico PR
00612 Urb Costas del Atlantico Puerto Rico PR
00612 Urb El Paraiso Puerto Rico PR
00612 Urb El Plantio Puerto Rico PR
00612 Urb Garcia Puerto Rico PR
00612 Urb Garden View Puerto Rico PR
00612 Urb Las Brisas Puerto Rico PR
00612 Urb Los Llanos Puerto Rico PR
00612 Urb Los Pinos Puerto Rico PR
00612 Urb Marisol Puerto Rico PR
00612 Urb Martell Puerto Rico PR
00612 Urb Ocean View Puerto Rico PR
00612 Urb Puertas del Este Puerto Rico PR
00612 Urb Radioville Puerto Rico PR
00612 Urb Regional Puerto Rico PR
00612 Urb San Daniel Puerto Rico PR
00612 Urb San Felipe Puerto Rico PR
00612 Urb San Lorenzo Puerto Rico PR
00612 Urb San Rafael Puerto Rico PR
00612 Urb Tanama Puerto Rico PR
00612 Urb University Court Apts Puerto Rico PR
00612 Urb University Gdns Puerto Rico PR
00612 Urb Victor Rojas 1 Puerto Rico PR
00612 Urb Victor Rojas 2 Puerto Rico PR
00612 Urb Villamar Puerto Rico PR
00612 Urb Vista Azul Puerto Rico PR
00612 Urb Vista del Atlantico Puerto Rico PR
00612 Valle Escondido Puerto Rico PR
00612 Villa Altamira Puerto Rico PR
00612 Villa de Lucia Puerto Rico PR
00612 Villa Los Santos Puerto Rico PR
00612 Villa Lucia Puerto Rico PR
00612 Villa Serena Puerto Rico PR
00612 Villa Sol Puerto Rico PR
00612 Villa Toledo Puerto Rico PR
00612 Villas del Capitan Puerto Rico PR
00612 Zeno Gandia Puerto Rico PR
00613 Arecibo Puerto Rico PR
00614 Arecibo Puerto Rico PR
00616 Bajadero Puerto Rico PR
00617 Barceloneta Puerto Rico PR
00617 Bda Catalana Puerto Rico PR
00617 Bda Catalina Puerto Rico PR
00617 Bo Magueyes Puerto Rico PR
00617 Bo Palenque Puerto Rico PR
00617 Bo Sabana Aguila Puerto Rico PR
00617 Est de Florida Puerto Rico PR
00617 Ext Parc Punta Palmas Puerto Rico PR
00617 Parc Imbery Puerto Rico PR
00617 Parc Magueyes Puerto Rico PR
00617 Parc Palenque Puerto Rico PR
00617 Parc Tiburon Puerto Rico PR
00617 Parc Tiburones 3 Puerto Rico PR
00617 Repto Las Llanadas Puerto Rico PR
00617 Sect Tiburon 3 Puerto Rico PR
00617 Urb Brisas de Llanadas Puerto Rico PR
00617 Urb Brisas del Monte Puerto Rico PR
00617 Urb Cataluna Puerto Rico PR
00617 Urb Las Delicias Puerto Rico PR
00617 Urb Las Llanadas Puerto Rico PR
00617 Urb Prado Alto Puerto Rico PR
00617 Urb San Francisco Puerto Rico PR
00617 Villa Barcelona Puerto Rico PR
00617 Villa Georgetti Puerto Rico PR
00617 Villas de la Sabana Puerto Rico PR
00622 Boqueron Puerto Rico PR
00622 Villa Taina Puerto Rico PR
00623 Alts del Mar Puerto Rico PR
00623 Bo Ballaja Puerto Rico PR
00623 Bo Monte Grande Puerto Rico PR
00623 Cabo Rojo Puerto Rico PR
00623 Ext La Concepcion Puerto Rico PR
00623 Ext Parc Elizabeth Puerto Rico PR
00623 Ext Sierra Linda Puerto Rico PR
00623 Jard del Puerto Puerto Rico PR
00623 Parc Betances Puerto Rico PR
00623 Parc Elizabeth Puerto Rico PR

[caiyunxia]

不用游标
应该没问题
好好参考UPDATE帮助
可以用变量

[caiyunxia]

CREATE TABLE #Tmp (Thid Char(1),other int)

INSERT INTO #Tmp VALUES ('a',1)
INSERT INTO #Tmp VALUES ('a',1)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('b',0)
INSERT INTO #Tmp VALUES ('c',2)
INSERT INTO #Tmp VALUES ('c',2)
--加索引，保証相同的組在同一位置上
CREATE NONCLUSTERED INDEX Idx_Test ON #tmp (Thid,other ASC)

DECLARE @Thid CHAR(1)
,@Other INT
SET @Other=0
UPDATE tmp
SET @Other=CASE WHEN @Thid=Thid THEN @Other+1 ELSE Other END
,@Thid=CASE WHEN COALESCE(@Thid,'')=Thid THEN @THid ELSE Thid END
,Other=@Other

FROM #tmp Tmp
SELECT* FROM #Tmp
DROP TABLE #tmp

[caiyunxia]

可以参考上面的例子