--如果加上排序 select * from( select a1,a2,a3,b2=null from 表a union all select a1,null,null,b2 from 表b )a order by case when a1 is null then 1 else 0 end,a1
--写错一个字段:select a1,a2,a3,b2=null from 表a union all select b1,null,null,b2 from 表b
--测试--测试数据 create table 表a(a1 int,a2 varchar(10),a3 int) insert 表a select 1,'aa',33 union all select 1,'bb',44 union all select 2,'cc',55 union all select 2,'dd',66create table 表b(b1 int,b2 int) insert 表b select 1 ,77 union all select 2 ,110 union all select null,187 go--如果加上排序 select a1,a2,a3,b2 from( select a1,a2,a3,b2=null,sid=0 from 表a union all select b1,null,null,b2,sid=1 from 表b )a order by case when a1 is null then 1 else 0 end,a1,sid go--删除测试 drop table 表a,表b/*--测试结果a1 a2 a3 b2 ----------- ---------- ----------- ----------- 1 aa 33 NULL 1 bb 44 NULL 1 NULL NULL 77 2 cc 55 NULL 2 dd 66 NULL 2 NULL NULL 110 NULL NULL NULL 187(所影响的行数为 7 行)--*/
select * from(
select a1,a2,a3,b2=null from 表a
union all
select a1,null,null,b2 from 表b
)a order by case when a1 is null then 1 else 0 end,a1
union all
select b1,null,null,b2 from 表b
create table 表a(a1 int,a2 varchar(10),a3 int)
insert 表a select 1,'aa',33
union all select 1,'bb',44
union all select 2,'cc',55
union all select 2,'dd',66create table 表b(b1 int,b2 int)
insert 表b select 1 ,77
union all select 2 ,110
union all select null,187
go--如果加上排序
select a1,a2,a3,b2
from(
select a1,a2,a3,b2=null,sid=0 from 表a
union all
select b1,null,null,b2,sid=1 from 表b
)a order by case when a1 is null then 1 else 0 end,a1,sid
go--删除测试
drop table 表a,表b/*--测试结果a1 a2 a3 b2
----------- ---------- ----------- -----------
1 aa 33 NULL
1 bb 44 NULL
1 NULL NULL 77
2 cc 55 NULL
2 dd 66 NULL
2 NULL NULL 110
NULL NULL NULL 187(所影响的行数为 7 行)--*/