【百分紧急求救一个查询】感谢各位老师

[wipe_tear]

---------------------------下面是一个表的select *查询，表名字叫做biao1吧
name1 name2 name3
1 2 3
---------------------------下面是一个表的select *查询，表名字叫做biao2吧
name4 name5 name6
4 5 6
7 8 9
10 11 12
===================================================

我想用一个查询，能查出来以下效果，

name1 name2 name3 name4 name5 name6
1 2 3 4 5 6
7 8 9
10 11 12


非常感谢，我各种连接都弄了，实在整不出来了，呵呵，非常感谢各位老师

[wipe_tear]

name1 name2 name3 name4 name5 name6
1 2 3 4 5 6
7 8 9
10 11 12


效果是上面的

[wipe_tear]

name1 name2 name3 name4 name5 name6
1 2 3 4 5 6
7 8 9
10 11 12

效果是上面的，发出来和编辑的就不一样了，呵呵

[开着拖拉机泡妞]

[Quote=引用 1 楼 wipe_tear 的回复:]

name1 name2 name3 name4 name5 name6
1 2 3 4 5 6
7 8 9
10 11 12


效果是上面的
[/Quote]

到底是怎么对的，你把它分清楚点，看不啥明白

[wipe_tear]

name1 name2 name3 name4 name5 name6
1-------2-------3-------4-------5-------6
------------------------7-------8-------9
------------------------10-------11-------12

横线代表什么都没有，因为CSDN编辑不是所看即所得，所以发表之后效果就变了，只能用横线代替了


[Quote=引用 3 楼 travylee 的回复:]

引用 1 楼 wipe_tear 的回复:

name1 name2 name3 name4 name5 name6
1 2 3 4 5 6
7 8 9
10 11 12


效果是上面的


到底是怎么对的，你把它分清楚点，看不啥明白
[/Quote]

[wipe_tear]

说白了，就是把两个表的列给给粘贴上，表1如果是1行3列，表2如果是3行3列的话
我希望得到3行6列的效果

并且前三列除了第一行有数之外，其余的为NULL就可以

[开着拖拉机泡妞]

--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
create table [t1]([name1] int,[name2] int,[name3] int)
insert [t1]
select 1,2,3
--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2]([name4] int,[name5] int,[name6] int)
insert [t2]
select 4,5,6 union all
select 7,8,9 union all
select 10,11,12
select isnull(name1,'') name1,isnull(name2,'') name2,isnull(name3,'') name3,
name4,name5,name6 from(
select ROW_NUMBER()over(order by getdate()) as id,* from t1) a
right join(
select ROW_NUMBER()over(order by getdate()) as id,* from t2)b
on a.id=b.id

/*
name1	name2	name3	name4	name5	name6
1	2	3	4	5	6
0	0	0	7	8	9
0	0	0	10	11	12
*/

select name1,name2,name3,
name4,name5,name6 from(
select ROW_NUMBER()over(order by getdate()) as id,* from t1) a
right join(
select ROW_NUMBER()over(order by getdate()) as id,* from t2)b
on a.id=b.id

/*
name1	name2	name3	name4	name5	name6
1	2	3	4	5	6
NULL	NULL	NULL	7	8	9
NULL	NULL	NULL	10	11	12
*/

[开着拖拉机泡妞]

--SQL 2000用临时表处理
--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
create table [t1]([name1] int,[name2] int,[name3] int)
insert [t1]
select 1,2,3
--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2]([name4] int,[name5] int,[name6] int)
insert [t2]
select 4,5,6 union all
select 7,8,9 union all
select 10,11,12


create table #t1(
id int identity(1,1),
[name1] int,[name2] int,[name3] int
)
insert #t1
select * from t1
create table #t2(
id int identity(1,1),
[name4] int,[name5] int,[name6] int
)
insert #t2
select * from t2

select name1,name2,name3,name4,name5,name6 
from #t1 a right join #t2 b on a.id=b.id

/*
name1	name2	name3	name4	name5	name6
1	2	3	4	5	6
NULL	NULL	NULL	7	8	9
NULL	NULL	NULL	10	11	12
*/

[dawugui]

--sql 2000

create table t1(name1 int,name2 int,name3 int)
insert into t1 values(1 ,2, 3)
create table t2(name4 int,name5 int,name6 int)
insert into t2 values(4  ,5  ,6)
insert into t2 values(7  ,8  ,9)
insert into t2 values(10 ,11 ,12)
go

select m.name1,
       m.name2,
       m.name3,
       n.name4,
       n.name5,
       n.name6
from
(select t.* , px = (select count(1) from t1 where name1 < t.name1) + 1 from t1 t) m
full join
(select t.* , px = (select count(1) from t2 where name4 < t.name4) + 1 from t2 t) n
on m.px = n.px
/*
name1       name2       name3       name4       name5       name6       
----------- ----------- ----------- ----------- ----------- ----------- 
1           2           3           4           5           6
NULL        NULL        NULL        7           8           9
NULL        NULL        NULL        10          11          12

（所影响的行数为 3 行）
*/

select isnull(cast(m.name1 as varchar),'') name1,
       isnull(cast(m.name2 as varchar),'') name2,
       isnull(cast(m.name3 as varchar),'') name3,
       isnull(cast(n.name4 as varchar),'') name4,
       isnull(cast(n.name5 as varchar),'') name5,
       isnull(cast(n.name6 as varchar),'') name6
from
(select t.* , px = (select count(1) from t1 where name1 < t.name1) + 1 from t1 t) m
full join
(select t.* , px = (select count(1) from t2 where name4 < t.name4) + 1 from t2 t) n
on m.px = n.px
/*
name1                          name2                          name3                          name4                          name5                          name6                          
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
1                              2                              3                              4                              5                              6
                                                                                             7                              8                              9
                                                                                             10                             11                             12

（所影响的行数为 3 行）

*/

drop table t1 , t2

[dawugui]

--sql 2005

create table t1(name1 int,name2 int,name3 int)
insert into t1 values(1 ,2, 3)
create table t2(name4 int,name5 int,name6 int)
insert into t2 values(4  ,5  ,6)
insert into t2 values(7  ,8  ,9)
insert into t2 values(10 ,11 ,12)
go

select m.name1,
       m.name2,
       m.name3,
       n.name4,
       n.name5,
       n.name6
from
(select t.* , px = row_number() over(order by name1) from t1 t) m
full join
(select t.* , px = row_number() over(order by name4) from t2 t) n
on m.px = n.px
/*
name1       name2       name3       name4       name5       name6
----------- ----------- ----------- ----------- ----------- -----------
1           2           3           4           5           6
NULL        NULL        NULL        7           8           9
NULL        NULL        NULL        10          11          12

(3 行受影响)
*/

select isnull(cast(m.name1 as varchar),'') name1,
       isnull(cast(m.name2 as varchar),'') name2,
       isnull(cast(m.name3 as varchar),'') name3,
       isnull(cast(n.name4 as varchar),'') name4,
       isnull(cast(n.name5 as varchar),'') name5,
       isnull(cast(n.name6 as varchar),'') name6
from
(select t.* , px = row_number() over(order by name1) from t1 t) m
full join
(select t.* , px = row_number() over(order by name4) from t2 t) n
on m.px = n.px
/*
name1                          name2                          name3                          name4                          name5                          name6
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
1                              2                              3                              4                              5                              6
                                                                                             7                              8                              9
                                                                                             10                             11                             12

(3 行受影响)
*/

drop table t1 , t2

[开着拖拉机泡妞]

--SQL 2000用临时表处理
--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
create table [t1]([name1] int,[name2] int,[name3] int)
insert [t1]
select 1,2,3
--> 测试数据：[t2]
if object_id('[t2]') is not null drop table [t2]
create table [t2]([name4] int,[name5] int,[name6] int)
insert [t2]
select 4,5,6 union all
select 7,8,9 union all
select 10,11,12


create table #t1(
id int identity(1,1),
[name1] int,[name2] int,[name3] int
)
insert #t1
select * from t1
create table #t2(
id int identity(1,1),
[name4] int,[name5] int,[name6] int
)
insert #t2
select * from t2

select name1,name2,name3,name4,name5,name6 
from #t1 a right join #t2 b on a.id=b.id

/*
name1    name2    name3    name4    name5    name6
1    2    3    4    5    6
NULL    NULL    NULL    7    8    9
NULL    NULL    NULL    10    11    12
*/

[把最好的留到最后]

+1 这个好理解[Quote=引用 10 楼 travylee 的回复:]
SQL code


--SQL 2000用临时表处理
--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
create table [t1]([name1] int,[name2] int,[name3] int)
insert [t1]
select 1,2,3
--> 测试数据：[t2]
if ob……
[/Quote]

[zhouixi]

[Quote=引用 13 楼 q465897859 的回复:]
SQL code
create table t1(name1 int,name2 int,name3 int)
insert into t1 values(1 ,2, 3)
create table t2(name4 int,name5 int,name6 int)
insert into t2 values(4 ,5 ,6)
insert into t2 values(7 ,8 ……
[/Quote]

你的语句中间，为啥一定要写4。才行。其他的都不对阿。

[zhouixi]

[Quote=引用 10 楼 travylee 的回复:]
SQL code

--SQL 2000用临时表处理
--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
create table [t1]([name1] int,[name2] int,[name3] int)
insert [t1]
select 1,2,3
--> 测试数据：[t2]
if obje……
[/Quote]

咋办，我看不懂乌龟写的

[q465897859]

create table t1(name1 int,name2 int,name3 int)
insert into t1 values(1 ,2, 3)
create table t2(name4 int,name5 int,name6 int)
insert into t2 values(4  ,5  ,6)
insert into t2 values(7  ,8  ,9)
insert into t2 values(10 ,11 ,12)


select case  when b.name4=4 then a.name1 else null end name1,
       case  when b.name4=4 then a.name2 else null end name2,
       case  when b.name4=4 then a.name3 else null end name3, b.* 
       from t1 a cross join t2 b 

[把最好的留到最后]

看不懂先用，以后会懂的。[Quote=引用 12 楼 zhouixi 的回复:]

引用 10 楼 travylee 的回复:
SQL code

--SQL 2000用临时表处理
--> 测试数据：[t1]
if object_id('[t1]') is not null drop table [t1]
create table [t1]([name1] int,[name2] int,[name3] int)
insert [t1]
select 1,2,3……
[/Quote]