Office中国论坛/Access中国论坛

标题: 求助：如何实现按照地区分别连续编号？ [打印本页]

作者: yanghua1900363 时间: 2012-2-9 16:21
标题: 求助：如何实现按照地区分别连续编号？
本帖最后由 yanghua1900363 于 2012-2-9 16:30 编辑

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

作者: nxjswt 时间: 2012-2-22 14:59
本帖最后由 nxjswt 于 2012-2-22 15:05 编辑

可以按照省份各自设计一个新表，例如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 时间: 2012-2-22 17:18
本帖最后由 todaynew 于 2012-2-22 17:42 编辑

方法一：用子查询处理
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;

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)