34,587
社区成员
发帖
与我相关
我的任务
分享
DECLARE @a table(id INT, city varchar(20), citycode varchar(20))
insert @a select 1 ,'武汉' ,'A'
union all select 2 ,'黄石' ,'B'
union all select 3 ,'十堰' ,'C'
DECLARE @b table(id INT, area varchar(20), citycode varchar(20), areacode char(2))
insert @b select 1 ,'江岸区' ,'A' ,'01'
union all select 2 ,'江汉区' ,'A' ,'02'
union all select 3 ,'黄石港区' ,'B' ,'01'
union all select 4 ,'西塞山区' ,'B' ,'02'
union all select 5 ,'茅箭区' ,'C' ,'01'
union all select 6 ,'张湾区' ,'C' ,'02'
select
id,
city,
citycode,
'' as areacode
from @a
union
select
id,
area,
citycode,
areacode
from @b
order by citycode,areacode
/*Result*/
id city citycode areacode
----------- -------------------- -------------------- --------
1 武汉 A
1 江岸区 A 01
2 江汉区 A 02
2 黄石 B
3 黄石港区 B 01
4 西塞山区 B 02
3 十堰 C
5 茅箭区 C 01
6 张湾区 C 02
(9 row(s) affected)
DECLARE @a table(id INT, city nvarchar(20), citycode varchar(20))
insert @a select 1 ,N'武汉' ,'A'
union all select 2 ,N'黄石' ,'B'
union all select 3 ,N'十堰' ,'C'
DECLARE @b table(id INT, area nvarchar(20), citycode varchar(20), areacode char(2))
insert @b select 1 ,N'江岸区' ,'A' ,'01'
union all select 2 ,N'江汉区' ,'A' ,'02'
union all select 3 ,N'黄石港区' ,'B' ,'01'
union all select 4 ,N'西塞山区' ,'B' ,'02'
union all select 5 ,N'茅箭区' ,'C' ,'01'
union all select 6 ,N'张湾区' ,'C' ,'02'
select id=row_number() over (order by citycode,
(case when isnumeric(code)=1 then code else '00' end)),
city,code from (select city,citycode,citycode code from @a
union all select area,citycode,areacode from @b) t
--> 测试数据:[表1]
if object_id('[表1]') is not null drop table [表1]
create table [表1]([id] int,[city] varchar(4),[citycode] varchar(1))
insert [表1]
select 1,'武汉','A' union all
select 2,'黄石','B' union all
select 3,'十堰','C'
--> 测试数据:[表2]
if object_id('[表2]') is not null drop table [表2]
create table [表2]([id] int,[area] varchar(8),[citycode] varchar(1),[areacode] varchar(2))
insert [表2]
select 1,'江岸区','A','01' union all
select 2,'江汉区','A','02' union all
select 3,'黄石港区','B','01' union all
select 4,'西塞山区','B','02' union all
select 5,'茅箭区','C','01' union all
select 6,'张湾区','C','02'
select case id when 1 then 1 else id+(select count(1) from 表2 where citycode<t.citycode) end as id,
city as name,citycode as code from 表1 t
union all
select id+(select count(1) from 表1 where citycode<=r.citycode),area,areacode
from 表2 r
order by id
------------------------------------------
1 武汉 A
2 江岸区 01
3 江汉区 02
4 黄石 B
5 黄石港区 01
6 西塞山区 02
7 十堰 C
8 茅箭区 01
9 张湾区 02
SET NOCOUNT ON
DECLARE @a table(id INT, city varchar(20), citycode varchar(20))
insert @a select 1 ,'武汉' ,'A'
union all select 2 ,'黄石' ,'B'
union all select 3 ,'十堰' ,'C'
DECLARE @b table(id INT, area varchar(20), citycode varchar(20), areacode char(2))
insert @b select 1 ,'江岸区' ,'A' ,'01'
union all select 2 ,'江汉区' ,'A' ,'02'
union all select 3 ,'黄石港区' ,'B' ,'01'
union all select 4 ,'西塞山区' ,'B' ,'02'
union all select 5 ,'茅箭区' ,'C' ,'01'
union all select 6 ,'张湾区' ,'C' ,'02'
SELECT id=identity(int,1,1),city,COALESCE(areacode,citycode) code INTO # FROM
(SELECT *,NULL areacode FROM @a UNION ALL SELECT * FROM @b)aa
ORDER BY citycode,areacode
SELECT * FROM #
DROP TABLE #
--result
/*id city code
----------- -------------------- --------------------
1 武汉 A
2 江岸区 01
3 江汉区 02
4 黄石 B
5 黄石港区 01
6 西塞山区 02
7 十堰 C
8 茅箭区 01
9 张湾区 02
*/