※※※※※※※※※※求一SQL语句※※※※※※※※※※

[heyixiang]

表A
num
----
1
2
3
4
5
6

表B
char
----
a
b
c
d



想得到的结果
num char
---- ----
1 a
2 b
3 c
4 d
5 null
6 null


2个表直接没有任何关联

[pbsql]

select id=identity(int,1,1),num into #ta from a
select id=identity(int,1,1),[char] into #tb from b
select #ta.num,#tb.[char] from #ta full join #tb on #ta.id=#tb.id
drop table #ta,#tb

[子陌红尘]

select
m.num,
n.char
from
(select a1.num,count(a2.num) as ID from 表A a1,表A a2 where a1.num >= a2.num group by a1.num) m
full outer join
(select b1.char,count(b2.char) as ID from 表B b1,表B b2 where b1.char >= b2.char group by b1.char) n
on
m.ID = n.ID

[lsxaa]

select id=identity(int,1,1),* into #t1 from A order by num

select id=identity(int,1,1),* into #t2 from B order by [char]

select a.num,b.[char]
from #t a full join #t b on a.id=b.id

[xluzhong]

create table a (id int)
insert into a select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6
create table b(c nvarchar(10))
insert into b select 'a' union all select 'b' union all select 'c' union all select 'd'

select *
from a
left join b
on a.id=ascii(upper(b.c))-64

drop table a
drop table b

[是是非非]

CREATE TABLE ta (V INTEGER)
CREATE TABLE tb (X VARCHAR (2))

INSERT INTO ta VALUEs(1)
INSERT INTO ta VALUEs(2)
INSERT INTO ta VALUEs(3)
INSERT INTO ta VALUEs(4)
INSERT INTO ta VALUEs(5)
INSERT INTO ta VALUEs(6)

insert into tb values ('a')
insert into tb values ('b')
insert into tb values ('c')

select t1.[v],t2.[x]
from (
select (
select count(*) from ta as t0 where t0.[v]<=ta.[v]
) as [IND],[v]
from ta
) as t1
left join (
select (
select count(*) from tb as t0 where t0.[x]<=tb.[x]
) as [IND],[x]
from tb
) as t2
on t2.[ind]=t1.[ind]

drop table tb
drop table ta

[heyixiang]

忘了说了，这两个表都是临时表

select tempColumn from split('1,2,3,4,5,6',',')
select tempColumn from split('a,b,c,d',',')

pbsql(风云)的方法不行啊，libin_ftsafe(子陌红尘) 的方法我还没看明白。



附上split函数
/*************************************************************
** File: fnSplit.sql
** Name: fnSplit
** Description: Split the string.
** Return values: @temp
** Parameters: @sql,@splits
** Author: MyGodness
** Date: 2005-1-11
*************************************************************/

CREATE Function Split(@Sql varchar(8000),@Splits varchar(10))
returns @temp Table (tempColumn varchar(100))
As
Begin
Declare @i Int
Set @Sql = RTrim(LTrim(@Sql))
Set @i = CharIndex(@Splits,@Sql)
While @i >= 1
Begin
Insert @temp Values(Left(@Sql,@i-1))
Set @Sql = SubString(@Sql,@i+1,Len(@Sql)-@i)
Set @i = CharIndex(@Splits,@Sql)
End

If @Sql <> ''
Insert @temp Values (@Sql)
Return
End

[lsxaa]

我写的有点问题，改一下
select id=identity(int,1,1),* into #t1 from A order by num

select id=identity(int,1,1),* into #t2 from B order by [char]

select a.num,b.[char]
from #t1 a full join #t2 b on a.id=b.id

[heyixiang]

lsxaa(小李铅笔刀) 和 eglic(圪圪) 都可以，其他人的没有仔细看，不好意思，先给分了。


顺便问以下，创建的#t1和#t2表是什么，在哪里可以找到它？

[是是非非]

临时表