表1: actornum name add
1 liu csdn
2 li sina
3 wei baidu
表2: studentnum sex birthday idnum
123 男 19890402 51010211110000
234 女 19880000 52222312321544表1 和表2 是两个毫不相干的表 现在我想要把表1中的name 字段和表2的birthday字段分列显示到一个查询结果集中 该用什么样的select语句?就是说我要得到如下的结果:
name birthday
liu 19890402
li 19880000
wei
from 表1 a left join (select *,rn=row_number() over(order by getdate()) 表2 )b on a.actornum=b.rn
a.name,isnull(b.birthday ,'') as birthday---这里简单 取字段
from
表1 a
left join
(select *,rn=row_number() over(order by getdate()) 表2 )b ---为b加个排序列 与 表1关联
on
a.actornum=b.rn -------关联条件
create table t1(actornum int,name varchar(10),[add] varchar(10))
insert into t1 values(1 ,'liu', 'csdn')
insert into t1 values(2 ,'li' , 'sina')
insert into t1 values(3 ,'wei', 'baidu')
create table t2(studentnum int,sex varchar(10),birthday varchar(10),idnum varchar(20))
insert into t2 values(123, '男', '19890402' ,'51010211110000')
insert into t2 values(234, '女', '19880000' ,'52222312321544')
goselect isnull(m.name ,'') name , isnull(n.birthday , '') birthday from
(select t.* , px = (select count(1) from t1 where actornum < t.actornum) + 1 from t1 t) m
full join
(select t.* , px = (select count(1) from t2 where studentnum < t.studentnum) + 1 from t2 t) n
on m.px = n.px
order by m.pxdrop table t1 , t2/*
name birthday
---------- ----------
liu 19890402
li 19880000
wei (所影响的行数为 3 行)
*/--sql 2005
create table t1(actornum int,name varchar(10),[add] varchar(10))
insert into t1 values(1 ,'liu', 'csdn')
insert into t1 values(2 ,'li' , 'sina')
insert into t1 values(3 ,'wei', 'baidu')
create table t2(studentnum int,sex varchar(10),birthday varchar(10),idnum varchar(20))
insert into t2 values(123, '男', '19890402' ,'51010211110000')
insert into t2 values(234, '女', '19880000' ,'52222312321544')
goselect isnull(m.name ,'') name , isnull(n.birthday , '') birthday from
(select t.* , px = row_number() over(order by actornum) from t1 t) m
full join
(select t.* , px = row_number() over(order by studentnum) from t2 t) n
on m.px = n.px
order by m.pxdrop table t1 , t2/*
name birthday
---------- ----------
liu 19890402
li 19880000
wei (3 行受影响)*/