select * , px = row_number() over(partition by ... order by ...) from ( select * from a union all select * from b )
select * from (select * from a union all select * from b)t row_number()over (....)...
if object_id('a') is not null drop table a go create table a(id int , name varchar(50)) go insert into a select 1 , 'aa' union all select 2 , 'bb' union all select 3 , 'cc' go if object_id('b') is not null drop table b go create table b(name varchar(50)) go insert into b select 'dd' union all select 'ee' union all select 'ff'go select * from a union all select row_number() over(order by name) as id , name from bid name ------------ 1 aa 2 bb 3 cc 1 dd 2 ee 3 ff
(
select * from a
union all
select * from b
)
(select * from a
union all
select * from b)t
row_number()over (....)...
if object_id('a') is not null
drop table a
go
create table a(id int , name varchar(50))
go
insert into a
select 1 , 'aa' union all
select 2 , 'bb' union all
select 3 , 'cc'
go
if object_id('b') is not null
drop table b
go
create table b(name varchar(50))
go
insert into b
select 'dd' union all
select 'ee' union all
select 'ff'go
select * from a
union all
select row_number() over(order by name) as id , name from bid name
------------
1 aa
2 bb
3 cc
1 dd
2 ee
3 ff