---------------------------下面是一个表的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
非常感谢,我各种连接都弄了,实在整不出来了,呵呵,非常感谢各位老师
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
非常感谢,我各种连接都弄了,实在整不出来了,呵呵,非常感谢各位老师
1 2 3 4 5 6
7 8 9
10 11 12
效果是上面的
1 2 3 4 5 6
7 8 9
10 11 12效果是上面的,发出来和编辑的就不一样了,呵呵
1-------2-------3-------4-------5-------6
------------------------7-------8-------9
------------------------10-------11-------12横线代表什么都没有,因为CSDN编辑不是所看即所得,所以发表之后效果就变了,只能用横线代替了
我希望得到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
*/
--> 测试数据:[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 t2select 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
*/
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)
goselect 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
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)
goselect 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 t2select 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
*/
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