这样的查询有更好的办法吗？

[19191919]

t1表数据为：
a1 a2
------------
a 10
a 20
b 40
b 50

t2表数据为：
b1 b2
----------
a 11
a 21
a 31
b 30

最终要得到的结果为:
a1/b1 a2 b2
----------------------------------
a 10 11
a 20 21
a 31
b 40 30
b 50

测试数据如下：
declare @t1 table(a1 char(1),a2 int)
declare @t2 table(b1 char(1),b2 int)

insert into @t1
select 'a',10 union
select 'a',20 union
select 'b',40 union
select 'b',50

insert into @t2
select 'a',11 union
select 'a',21 union
select 'a',31 union
select 'b',30

[8992026]

declare @t1 table(a1 char(1),a2 int)
declare @t2 table(b1 char(1),b2 int)

insert into @t1
select 'a',10 union
select 'a',20 union
select 'b',40 union
select 'b',50

insert into @t2
select 'a',11 union
select 'a',21 union
select 'a',31 union
select 'b',30

select isnull(x.a1,y.b1) as [a1/b1],a2,b2
from (
select *,(select count(*) as id from @t1 where a1=a.a1 and a2<=a.a2) as id from @t1 a
) as x full join (
select *,(select count(*) as id from @t2 where b1=a.b1 and b2<=a.b2) as id from @t2 a
) as y
on x.a1=y.b1 and x.id=y.id

--结果：

a1/b1 a2 b2
----- ----------- -----------
a 10 11
a 20 21
a NULL 31
b 40 30
b 50 NULL

（所影响的行数为 5 行）

[playyuer]

键?

[8992026]

如果需要把null换成空格

select isnull(x.a1,y.b1) as [a1/b1],isnull(a2,'') as a2,isnull(b2,'') as b2
from (
select *,(select count(*) as id from @t1 where a1=a.a1 and a2<=a.a2) as id from @t1 a
) as x full join (
select *,(select count(*) as id from @t2 where b1=a.b1 and b2<=a.b2) as id from @t2 a
) as y
on x.a1=y.b1 and x.id=y.id

[8992026]

双规干部:
想说什么？

[zheninchangjiang]

select isnull(a.b1,b.a1),b.a2,a.b2 from
(select a.*,id=(select count(*) from t2 b where b.b1=a.b1 and b.b2<=a.b2) from t2 a)
a full join
(select a.*,id=(select count(*) from t1 b where b.a1=a.a1 and b.a2<=a.a2) from t1 a) b
on a.b1=b.a1 and a.id=b.id

[zjcxc]

declare @t1 table(a1 char(1),a2 int)
declare @t2 table(b1 char(1),b2 int)

insert into @t1
select 'a',10 union
select 'a',20 union
select 'b',40 union
select 'b',50

insert into @t2
select 'a',11 union
select 'a',21 union
select 'a',31 union
select 'b',30

--查询
select [a1/b1]=isnull(a.a1,b.b1),a2,b2
from @t1 a full join @t2 b on a.a1=b.b1
and (select count(*) from @t1 where a1=a.a1 and a2<=a.a2)
=(select count(*) from @t2 where b1=b.b1 and b2<=b.b2)
order by [a1/b1]

/*--测试结果

a1/b1 a2 b2
----- ----------- -----------
a 10 11
a 20 21
a NULL 31
b 40 30
b 50 NULL

（所影响的行数为 5 行）
--*/

[zjcxc]

要求,对于t1表,同一a1/a2的值唯一,而且结果按a1/a2排序
t2表的要求相同

[zjcxc]

否则用临时表+标识字段

[19191919]

多谢各位，原先我只想到用临时表+标识字段，没想到用相关联子查询和聚集函数，哈哈，又知道一种方法，谢谢，真是很感谢。：）
结贴。：）