22,209
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#用户表') is null
drop table #用户表
Go
Create table #用户表([ID] int,[姓名] nvarchar(22),[省] nvarchar(22),[市] nvarchar(22),[区] nvarchar(22) )
Insert #用户表
select 1,N'张三',10000,null,null union all
select 2,N'李四',10000,null,null union all
select 3,N'王五',10000,null,null union all
select 4,N'赵六',10000,null,null
GO
if not object_id(N'Tempdb..#区县表') is null
drop table #区县表
Go
Create table #区县表([ID] int,[编号] nvarchar(23),[名字] nvarchar(23),[市] int)
Insert #区县表
select 1,'100110',N'石碣镇',100100 union all
select 2,'100120',N'石龙镇',100100 union all
select 3,'100210',N'福田区',100200
Go
--测试数据结束
SELECT t1.ID,
t1.姓名,
t1.省,
t2.市,
t2.编号
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY SUBSTRING(省, 1, 3) ORDER BY ID) AS rn1
FROM #用户表
) t1
JOIN
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY SUBSTRING(编号, 1, 3) ORDER BY ID) - 1 AS rn1,
COUNT(1) OVER (PARTITION BY SUBSTRING(编号, 1, 3)) AS rn2
FROM #区县表
) t2
ON t2.rn1 = t1.rn1 % t2.rn2;