select a.id,a.name,a.price from (select id,name,price,1 as id from t1 union all select id,name,price,2 as id from t2) a order by a.id,(case a.id when 1 then a.price else 1 end)
--生成测试数据 create table #t1(id int,name char(1),price int) insert into #t1 select 1,'a',100 insert into #t1 select 2,'b',120 insert into #t1 select 3,'c',110 insert into #t1 select 4,'d',160 insert into #t1 select 5,'e',150create table #t2(id int,name char(1),price int) insert into #t2 select 6,'f',80 insert into #t2 select 7,'g',20 insert into #t2 select 8,'h',10 insert into #t2 select 9,'i',60 insert into #t2 select 10,'j',50--执行查询 select a.id,a.name,a.price from (select id,name,price,1 as nid from #t1 union all select id,name,price,2 as nid from #t2) a order by a.nid,(case a.nid when 1 then a.price else 1 end)--输出结果 /* id name price ---- ---- ----- 1 a 100 3 c 110 2 b 120 5 e 150 4 d 160 6 f 80 7 g 20 8 h 10 9 i 60 10 j 50*/--删除测试数据 drop table #t1,#t2
a.id,a.name,a.price
from
(select id,name,price,1 as id from t1
union all
select id,name,price,2 as id from t2) a
order by
a.id,(case a.id when 1 then a.price else 1 end)
create table #t1(id int,name char(1),price int)
insert into #t1 select 1,'a',100
insert into #t1 select 2,'b',120
insert into #t1 select 3,'c',110
insert into #t1 select 4,'d',160
insert into #t1 select 5,'e',150create table #t2(id int,name char(1),price int)
insert into #t2 select 6,'f',80
insert into #t2 select 7,'g',20
insert into #t2 select 8,'h',10
insert into #t2 select 9,'i',60
insert into #t2 select 10,'j',50--执行查询
select
a.id,a.name,a.price
from
(select id,name,price,1 as nid from #t1
union all
select id,name,price,2 as nid from #t2) a
order by
a.nid,(case a.nid when 1 then a.price else 1 end)--输出结果
/*
id name price
---- ---- -----
1 a 100
3 c 110
2 b 120
5 e 150
4 d 160
6 f 80
7 g 20
8 h 10
9 i 60
10 j 50*/--删除测试数据
drop table #t1,#t2