select distinct A from tbl 返回结果如下:
a1
a2
a3select distinct B from tbl 返回结果如下:
b1
b2
b3
b4
b5我想返回如下的结果:
a1 b1
a2 b2
a3 b3
null b4
null b5请问怎么写?是不是要用到join?(目前我只会用cross join)
a1
a2
a3select distinct B from tbl 返回结果如下:
b1
b2
b3
b4
b5我想返回如下的结果:
a1 b1
a2 b2
a3 b3
null b4
null b5请问怎么写?是不是要用到join?(目前我只会用cross join)
返回如下结果也一样:
a1 b2
a2 b1
null b3
null b4
a3 b5
select * from
(select distinct A from tbl) a
right join
(select distinct B from tbl ) b
on 1=1
declare @t1 table(A varchar(3))
declare @t2 table(B varchar(3))
insert into @t1
select
'a1'
union all select
'a2'
union all select
'a3'insert into @t2
select
'b1'
union all select
'b2'
union all select
'b3'
union all select
'b4'
union all select
'b5'--有点无耻哈.^_^select * from @t1 a right join @t2 b on right(a,1)=right(b,1)
declare @t2 table(B varchar(3))
insert into @t1
select
'a1'
union all select
'a2'
union all select
'a3'insert into @t2
select
'b1'
union all select
'b2'
union all select
'b3'
union all select
'b4'
union all select
'b5'--有点无耻哈.^_^select * from @t1 a right join @t2 b on right(a,1)=right(b,1)
a1 b1
a2 b1
a3 b1
a1 b2
a2 b2
a3 b2
a1 b3
a2 b3
a3 b3
...
declare @t2 table(B varchar(3))
insert into @t1
select
'a1'
union all select
'a2'
union all select
'a3'insert into @t2
select
'b1'
union all select
'b2'
union all select
'b3'
union all select
'b4'
union all select
'b5'
select * from @t1,@t2
a1 b1
a2 b1
a3 b1
a1 b2
a2 b2
a3 b2
a1 b3
a2 b3
a3 b3
a1 b4
a2 b4
a3 b4
a1 b5
a2 b5
a3 b5(15 row(s) affected)
???
還是下面的格式???
a1 b1
a2 b2
a3 b3
null b4
null b5
declare @t2 table(B varchar(3))
insert into @t1
select
'a1'
union all select
'a2'
union all select
'a3'insert into @t2
select
'b1'
union all select
'b2'
union all select
'b3'
union all select
'b4'
union all select
'b5'
select id=identity(int,1,1),A into #T1 from @t1
select id=identity(int,1,1), B into #T2 from @t2
select #T1.A,#T2.B from #T2 left join #T1 on #T1.id=#T2.id
go
drop table #T1,#T2
a1 b1
a2 b2
a3 b3
null b4
null b5