请指教!!

[lvyichang]

A表
no name1
1 AAA
1 BBB
1 CCC
2 DDD

B表
no name2
1 LLL
1 MMM
2 OOO
2 PPP
2 QQQ

C表
no name3
1 111
2 222
2 333
---------------------------------
希望得到当no=1时
no name1 name2 name3
1 AAA LLL 111
1 BBB MMM
1 CCC

当no=2时
no name1 name2 name3
2 DDD OOO 222
2 PPP 333
2 QQQ
----------------------------------
也就是取三个表的最大行数拼起来!
不知道能否实现,望指导!万分感谢!!

[水族杰纶]

每个表同一序号
搞个ID left吧

[ws_hgo]

等等。。

[水族杰纶]

if not object_id('ta') is null
	drop table ta
Go
Create table ta([no] int,[name1] nvarchar(3))
Insert ta
select 1,N'AAA' union all
select 1,N'BBB' union all
select 1,N'CCC' union all
select 2,N'DDD'
Go
if not object_id('tb') is null
	drop table tb
Go
Create table tb([no] int,[name2] nvarchar(3))
Insert tb
select 1,N'LLL' union all
select 1,N'MMM' union all
select 2,N'OOO' union all
select 2,N'PPP' union all
select 2,N'QQQ'
Go
if not object_id('tc') is null
	drop table tc
Go
Create table tc([no] int,[name3] int)
Insert tc
select 1,111 union all
select 2,222 union all
select 2,333
Go
;with tta
as
(select *,px=row_number()over(order by [name1]) from ta where no=1)
, ttb
as
(select *,px=row_number()over(order by [name2]) from tb where no=1)
,ttc
as
(select *,px=row_number()over(order by [name3]) from tc where no=1)
select isnull(isnull(a.[NO],b.[No]),c.[NO])[NO],
       a.[name1],
       b.[name2],
       c.[name3]
from tta a full join ttb b on a.[px]=b.[px]
           full join ttc c on b.[px]=c.[px]
/*
NO          name1 name2 name3
----------- ----- ----- -----------
1           AAA   LLL   111
1           BBB   MMM   NULL
1           CCC   NULL  NULL
*/

[水族杰纶]

if not object_id('ta') is null
	drop table ta
Go
Create table ta([no] int,[name1] nvarchar(3))
Insert ta
select 1,N'AAA' union all
select 1,N'BBB' union all
select 1,N'CCC' union all
select 2,N'DDD'
Go
if not object_id('tb') is null
	drop table tb
Go
Create table tb([no] int,[name2] nvarchar(3))
Insert tb
select 1,N'LLL' union all
select 1,N'MMM' union all
select 2,N'OOO' union all
select 2,N'PPP' union all
select 2,N'QQQ'
Go
if not object_id('tc') is null
	drop table tc
Go
Create table tc([no] int,[name3] int)
Insert tc
select 1,111 union all
select 2,222 union all
select 2,333
Go
;with tta
as
(select *,px=row_number()over(order by [name1]) from ta where no=2)
, ttb
as
(select *,px=row_number()over(order by [name2]) from tb where no=2)
,ttc
as
(select *,px=row_number()over(order by [name3]) from tc where no=2)
select isnull(isnull(a.[NO],b.[No]),c.[NO])[NO],
       a.[name1],
       b.[name2],
       c.[name3]
from tta a full join ttb b on a.[px]=b.[px]
           full join ttc c on b.[px]=c.[px]
/*
NO          name1 name2 name3
----------- ----- ----- -----------
2           DDD   OOO   222
2           NULL  PPP   333
2           NULL  QQQ   NULL
*/

[fengyun142415]

当no=2时
no name1 name2 name3
2 DDD OOO 222
2 PPP 333
2 QQQ

[ws_hgo]

if object_id('A') is not null drop table A
create table A
(
  no int,
  name1 varchar(20)
)
insert into A select 1,'AAA'
union all select 1,'BBB'
union all select 1,'CCC'
union all select 2,'DDD'

if object_id('B') is not null drop table B
create table B
(
  no int,
  name2 varchar(20)
)
insert into B select 1,'LLL'
union all select 1,'MMM'
union all select 2,'OOO'
union all select 2,'PPP'
union all select 2,'QQQ'
if object_id('C') is not null drop table C
go
create table C
(
  no int,
  name3 varchar(20)
)
insert into C select 1,'111'
union all select 2,'222'
union all select 2,'333'
go

select A1.no,
       (case when A1.row1=1 then A1.name1 else null end) name1,
       (case when B1.row2=2 then B1.name2 else null end) name2,
       (case when C1.row3=3 then C1.name3 else null end) name3
from 
(
select *, 
       (select count(*)+1 from A where no=A1.no and name1<A1.name1) row1
from A A1 where no=1
) A1,
(
select *, 
       (select count(*)+1 from B where no=B1.no and name2<B1.name2) row2
from B B1 where no=1
) B1,
(
select *, 
       (select count(*)+1 from C where no=C1.no and name3<C1.name3) row3
from C C1 where no=1
) C1
order by row1,row2,row3

select A1.no,A1.name1,B1.name2,C1.name3
from
(
select *, 
       (select count(*)+1 from A where no=A1.no and name1<A1.name1) row1
from A A1 where no=1
) A1
full join
(
 select *, 
       (select count(*)+1 from B where no=B1.no and name2<B1.name2) row2
from B B1 where no=1
) B1
on A1.row1=B1.row2
full join
(
  select *, 
       (select count(*)+1 from C where no=C1.no and name3<C1.name3) row3
from C C1 where no=1
) C1
on A1.row1=C1.row3

no          name1                name2                name3
----------- -------------------- -------------------- --------------------
1           AAA                  LLL                  111
1           BBB                  MMM                  NULL
1           CCC                  NULL                 NULL

(3 行受影响)

[ws_hgo]

修改下


if object_id('A') is not null drop table A
create table A
(
  no int,
  name1 varchar(20)
)
insert into A select 1,'AAA'
union all select 1,'BBB'
union all select 1,'CCC'
union all select 2,'DDD'

if object_id('B') is not null drop table B
create table B
(
  no int,
  name2 varchar(20)
)
insert into B select 1,'LLL'
union all select 1,'MMM'
union all select 2,'OOO'
union all select 2,'PPP'
union all select 2,'QQQ'
if object_id('C') is not null drop table C
go
create table C
(
  no int,
  name3 varchar(20)
)
insert into C select 1,'111'
union all select 2,'222'
union all select 2,'333'
go
--第一问

select A1.no,A1.name1,B1.name2,C1.name3
from
(
select *, 
       (select count(*)+1 from A where no=A1.no and name1<A1.name1) row1
from A A1 where no=1
) A1
full join
(
 select *, 
       (select count(*)+1 from B where no=B1.no and name2<B1.name2) row2
from B B1 where no=1
) B1
on A1.row1=B1.row2
full join
(
  select *, 
       (select count(*)+1 from C where no=C1.no and name3<C1.name3) row3
from C C1 where no=1
) C1
on A1.row1=C1.row3

no          name1                name2                name3
----------- -------------------- -------------------- --------------------
1           AAA                  LLL                  111
1           BBB                  MMM                  NULL
1           CCC                  NULL                 NULL

(3 行受影响)
----第二问
select isnull(isnull(A1.no,B1.no),C1.no) no,A1.name1,B1.name2,C1.name3
from
(
select *, 
       (select count(*)+1 from A where no=A1.no and name1<A1.name1) row1
from A A1 where no=2
) A1
full join
(
 select *, 
       (select count(*)+1 from B where no=B1.no and name2<B1.name2) row2
from B B1 where no=2
) B1
on A1.row1=B1.row2
full join
(
  select *, 
       (select count(*)+1 from C where no=C1.no and name3<C1.name3) row3
from C C1 where no=2
) C1
on B1.row2=C1.row3

no          name1                name2                name3
----------- -------------------- -------------------- --------------------
2           DDD                  OOO                  222
2           NULL                 PPP                  333
2           NULL                 QQQ                  NULL

(3 行受影响)

[东那个升]

create table #ta(no int,name1 varchar(10))
insert #ta select 1 ,'AAA'
insert #ta select 1 ,'BBB'
insert #ta select 1 ,'CCC'
insert #ta select 2 ,'DDD'

create table #tb(no int,name2 varchar(10))
insert #tb select 1 ,'LLL'
insert #tb select 1 ,'MMM'
insert #tb select 2 ,'OOO'
insert #tb select 2 ,'PPP'
insert #tb select 2 ,'QQQ'


create table #tc(no int,name3 varchar(10))
insert #tc select 1 ,111
insert #tc select 2 ,222
insert #tc select 2 ,333


---1
declare @no int
set @no=1 ---no=1 or 2
;with cte as(
select no,'name1' as type ,name1 as name  from #ta  where no=@no
union all
select no,'name2' as type ,name2 from #tb   where no=@no
union all
select no,'name3' as type ,name3 from #tc   where no=@no
) 

select no,name1,name2,name3 from (
select *,rn=ROW_NUMBER() over(partition by type order by getdate()) from cte 
) a
pivot(max(name) for type in([name1],[name2],[name3])) pvt

[ws_hgo]

如果是sql2000的
只能使用7楼的答案

如果是sql2005的
3，7，8楼都行

[lvyichang]

非常非常感谢楼上各位!
我测试一下!!

[ws_hgo]

[Quote=引用 10 楼 lvyichang 的回复:]
非常非常感谢楼上各位!
我测试一下!!
[/Quote]

恩
没有问题的
就结贴