A表
no name1
1 AAA
1 BBB
1 CCC
2 DDDB表
no name2
1 LLL
1 MMM
2 OOO
2 PPP
2 QQQC表
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
----------------------------------
也就是取三个表的最大行数拼起来!
不知道能否实现,望指导!万分感谢!!
no name1
1 AAA
1 BBB
1 CCC
2 DDDB表
no name2
1 LLL
1 MMM
2 OOO
2 PPP
2 QQQC表
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吧
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
*/
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
*/
no name1 name2 name3
2 DDD OOO 222
2 PPP 333
2 QQQ
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'
goselect 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,row3select 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.row3no name1 name2 name3
----------- -------------------- -------------------- --------------------
1 AAA LLL 111
1 BBB MMM NULL
1 CCC NULL NULL(3 行受影响)
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.row3no 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.row3no name1 name2 name3
----------- -------------------- -------------------- --------------------
2 DDD OOO 222
2 NULL PPP 333
2 NULL QQQ NULL(3 行受影响)
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
只能使用7楼的答案如果是sql2005的
3,7,8楼都行