求助：如何实现按照地区分别连续编号？

yanghua1900363

请教各位一个问题：这里有两张表 “省份”表是包含省份数据的 “city”表是包含地市数据的，能否实现按照省份对地市分别自动连续编号？先行谢过！

nxjswt

可以按照省份各自设计一个新表，例如new,new1,new2等然后按照下面的追加查询

INSERT INTO new ( 所在省名称, City, CCode, LastEditDate, CreationDate )
SELECT 省份.所在省名称, city.City, city.CCode, 省份.LastEditDate, 省份.CreationDate
FROM 省份 INNER JOIN city ON 省份.provinceID = city.ProvinceID
WHERE (((省份.所在省名称)=[输入要建立自动编号的省份]));

todaynew

方法一：用子查询处理
SELECT a.CityID, a.City, a.ProvinceID, (select count(*) from city as b where b.ProvinceID=a.ProvinceID and b.CityID<=a.CityID ) AS CCode
FROM city AS a
ORDER BY a.ProvinceID, a.CityID;

方法二：用域函数处理

SELECT CityID, City, ProvinceID,Dcount("*" "city","ProvinceID=" & ProvinceID  & " and CityID<=" & CityID ) AS CCode
FROM city
ORDER BY ProvinceID, CityID;

方法三：用自定义函数处理
1、写一个自定义函数如下：
Function num(cityID As Long, PrID As Long) As Long
     Dim ssql As String
     Dim rs As New ADODB.Recordset
     ssql = "select * from city where ProvinceID=" & PrID & " and CityID<=" & cityID
     rs.Open ssql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
     num = rs.RecordCount
     rs.Close
End Function
2、查询中调用该函数：
SELECT city.CityID, city.City, city.ProvinceID, num([CityID],[ProvinceID]) AS CCode
FROM city
ORDER BY city.ProvinceID, city.CityID;